Where clause and Group by clause in TeraData

Ratings:
(4)
Views: 412
Banner-Img
Share this blog:

 

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 the group by clause, when we are going to perform AGGREGATE operation, a sum, Avg, Min, Max, etc—Operations.
  3. We use column names(or) Numbers to represent the data.
Party id Party Code Party income
1 10 10000
2 30 20000
3 20 40000
1 12 20000
2 30 60000
4 10 20000

Inclined to build a profession as Teradata Developer? Then here is the blog post on, explore Teradata Training

  Select*from Party order by Party id (OR) Order by 1àfirst column of*

Party id Party Code Party income
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
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

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   Distinct clause  

Note:

  • To eliminate duplicate while retrieval it is useful
  • Always use group by, it is used read 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

  Distinct clause  

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 from 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 the 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) from the group by pid having count(*)>1)

[Deleting old row by keeping new row]

For an in-depth knowledge, click on below

You liked the article?

Like: 0

Vote for difficulty

Current difficulty (Avg): Medium

EasyMediumHardDifficultExpert
IMPROVE ARTICLEReport Issue

About Author

Authorlogo
Name
TekSlate
Author Bio

TekSlate is the best online training provider in delivering world-class IT skills to individuals and corporates from all parts of the globe. We are proven experts in accumulating every need of an IT skills upgrade aspirant and have delivered excellent services. We aim to bring you all the essentials to learn and master new technologies in the market with our articles, blogs, and videos. Build your career success with us, enhancing most in-demand skills in the market.