SQL Server how can I use COUNT DISTINCT(*) in HAVING clause? -
i have procedure counts unique [customerid] values , displays them in select list. i'm trying sort [customerid] "> 1" using having clause, sql won't let me use distinct count inside having. in mind makes sense having should work count not:
use mycompany; go select distinct count(customerid) numberofordersmade, customerid customerid tblitems_ordered group customerid having distinct count(customerid) > 1 go
you want select count(distinct orderid)
instead of distinct count(customerid)
:
use mycompany; go select count(distinct orderid) numberofordersmade, customerid customerid tblitems_ordered group customerid having count(distinct orderid) > 1 go
when outside of count
, distinct
eliminate duplicate rows result set, have no effect in query because doing group by
. when inside count
, distinct
limit count unique values of column pass count function. thus, makes more sense use orderid column instead of customerid when you're aliasing numberofordersmade.
Comments
Post a Comment