Group Discounts available for 3+ students and Corporate Clients

Where clause and Group by clause in TeraData

Where clause

To filter the data while retrieving we use where clause

 

Ex

Display employee details, who are having department number 10

Select*from emp where DEPITD=10

 

 

ORDER BY CLAUSE

  1. It displays the data in the specified order ascending/desired
  2. We use column names (or)Numbers for storing the data

 

Group by clause

  1. It displays the data in group wise
  2. Generally recommended to go for group by clause, when we are going to perform AGGREGATE operation, sum as Sum, Avg, Min, max etc—Operations.
  3. We use column names(or) Numbers to represent the data
Party idParty CodeParty in come
11010000
23020000
32040000
11220000
23060000
41020000

 

Select*from Party order by Party id

(OR)

Order by 1àfirst column of*

Party idParty CodeParty in come
11010000
13010000
22040000
21220000
33060000
41020000

 

Select Party id, Sum(Party income) from party group By1

Party idSum
130000
280000
340000
420000

 

Select Party id(Aggregate), Party code Party income (Non- aggregate)from party group By1

Party id
1
2
3
4

 

 

We can’t use aggregate and non-aggregate files in the same select column

Select Party id, sum (Party income) Party loc

from party group By Party loc, Party id(or) Select min(Party id) sum (Party income) Party loc

from party group By Party loc

 

Identity Duplicate

a) In case single column employee id having duplicate

Select Party id, Count(*)from party group By Party id having –count(*)>1;

 

Wrong answer – Using distinct clause

Correct – Group by, having clause

Select PID, from party group by pid, pc having count(*)>1;

 

Screenshot_73

 

Select party id, party name, count(*) From party group by party I party name having count(*)>1,b) In case of multiple – column duplication

 

 

Correct query

  1. Select pid, p code, PI From party group by 1,2,3
  2. Select pid, max(PC)Sum(PI)From party group by 1

 

Distinct clause –  It displays distinct values of columns district max

Select Distinct party id from partyà select party id group part id

 

Screenshot_72

 

Note

To eliminate duplicate while retrieval it is useful

Always use group by, it is used red time

Never use distinct clause

 

Note

 Performance wise group by gives good performance because it occupies less spool memory

Display locations in ascending order and within the location names in descending order

Select*From party order by

 

Screenshot_74

 

Inserting duplicate into Another table

B                                             A

PID   PNAME              PID   PNAME

Insert into B(Select pid, pname from a group by pid,pno having count(*)>1) – -> Wrong

Insert into  B select pid, pname form a group by pid, pname having count(*)>1) – -> correct

 

 

Deleting the duplicate in the table

In oracle:- There are 3 ways

1) By using rank, row number, dense rank functions

2) By using an intermediate table

A

1

2

1

2

3

4

 

Intermediate table Create table B as(Select pid from A group by pid having count(*)>1)

B

1

2

 

Deletion

Delete from A,B Where A. Pid, B. Pid;

 

Removing either latest rows(or)old rows

Delete from a where row id>(Select min(row id)from

A group by pid having count(*)

[Deleting recent duplicates]

Delete from A where row id<(Select max(rowid) rom group by pid having count(*)>1)

[Deleting old row by keeping new row]

“At TekSlate, we are trying to create high quality tutorials and articles, if you think any information is incorrect or want to add anything to the article, please feel free to get in touch with us at info@tekslate.com, we will update the article in 24 hours.”

0 Responses on Where clause and Group by clause in TeraData"

    Leave a Message

    Your email address will not be published. Required fields are marked *

    Support


    Please Enter Your Details and Query.
    Three + 6