CODEWITHSUNDEEP
mysqlsql-server
sujith karivelil
sujith karivelil

Reputation: 29006

Using alias name in where clause - SQL

I have a Summary table (data_summary) and a Summary Details table (data_summary_dtl). I'm applying the following query in data_summary_dtl table to get the

Number of records in detail table for each record in the summary table,select data if count is greater than 2

select summary_gid,COUNT(summary_gid) as varCount 
from data_summary_dtl 
group by summary_gid;

I can list the summary_id along with the count using the following query,

Which gives fine result for me, now my question is that how can i obtain the result of records that having count greater than 2.

i had tried the following but not get the result as expected:

select summary_gid,COUNT(summary_gid) as varCount
from data_summary_dtl
where varCount > 2 
group by summary_gid; 

select summary_gid,COUNT(summary_gid) as varCount 
from data_summary_dtl
where COUNT(summary_gid) > 2
group by summary_gid;

Is there any other possibility to get the result?

Upvotes: 0

Views: 62

Answers (2)

DhruvJoshi
DhruvJoshi

Reputation: 17126

Please use this query

select summary_gid,COUNT(summary_gid) as varCount 
from  data_summary_dtl 
group by summary_gid
having COUNT(summary_gid)> 2

Having restricts the select list after applying the GROUP BY and used after it in query syntax. WHERE is used before GROUP BY and restricts the select list before grouping in GROUP BY, so your aggregate functions like COUNT() which depend on GROUP BY do not yield results

About your queries:

select summary_gid,COUNT(summary_gid) as varCount from  data_summary_dtl 
where varCount > 2 group by summary_gid;

here the error would be

Invalid column name varCount

because you can not use an alias defined in the SELECT list in WHERE or GROUP BY, ORDER or HAVING condition.

In second query

select summary_gid,COUNT(summary_gid) as varCount from  data_summary_dtl 
where COUNT(summary_gid) > 2 group by summary_gid;

you will receive error about

using aggregate function COUNT in WHERE clause

Please note that it is perfectly OK to use WHERE in GROUP BY syntax. Example if you wanted to see details about a summary_gid which are greater than 10 and less than 15 then you can use 

select summary_gid,COUNT(summary_gid) as varCount from  data_summary_dtl 
where summary_gid > 10 AND  summary_gid > 20 group by summary_gid;

You can also use WHERE and HAVING both with GROUP BY like this

select summary_gid,COUNT(summary_gid) as varCount from  data_summary_dtl 
where summary_gid > 10 AND  summary_gid > 20 group by summary_gid 
having COUNT(summary_gid)>1 ;

Upvotes: 2

Raging Bull
Raging Bull

Reputation: 18737

Use having clause along with group by:

select summary_gid,COUNT(summary_gid) as varCount 
from  data_summary_dtl 
group by summary_gid
having COUNT(summary_gid) > 2

We cannot use WHERE with aggregate functions. That is when HAVING clause comes to help.

Upvotes: 0

Related Questions