Using alias name in where clause - SQL

Question

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?

DhruvJoshi · Accepted Answer · 2015-08-08 10:15:16Z

2

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 ;

edited Aug 8, 2015 at 10:15

answered Aug 8, 2015 at 9:56

DhruvJoshi

17.2k6 gold badges46 silver badges63 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

DhruvJoshi Over a year ago

@un-lucky Thank you! I hope you also understood the role of where clause in group by query is only for pre-filtering the table before grouping.

Raging Bull · Accepted Answer · 2015-08-08 09:56:58Z

0

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.

edited Aug 8, 2015 at 9:56

answered Aug 8, 2015 at 9:54

Raging Bull

18.8k13 gold badges52 silver badges56 bronze badges

1 Comment

sujith karivelil Over a year ago

Invalid column name 'varCount'.

Collectives™ on Stack Overflow

Using alias name in where clause - SQL

2 Answers 2

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related