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



Display employee details, who are having department number 10

Select*from emp where DEPITD=10




  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 id Party Code Party in come
1 10 10000
2 30 20000
3 20 40000
1 12 20000
2 30 60000
4 10 20000


Select*from Party order by Party id


Order by 1àfirst column of*

Party id Party Code Party in come
1 10 10000
1 30 10000
2 20 40000
2 12 20000
3 30 60000
4 10 20000


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

Party id Sum
1 30000
2 80000
3 40000
4 20000


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

Party id



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;




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





To eliminate duplicate while retrieval it is useful

Always use group by, it is used red time

Never use distinct clause



 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




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









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






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, 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 *


Please Enter Your Details and Query.
Three + 6