• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 229
  • Last Modified:

Using column in the where/having clause but not in group by

I have a query like

Select Category, Sum (Cost), Format([OrderDate],"ww")
from OrderTable
GROUP BY Category, Format([OrderDate],"ww")
--HAVING customer <> 12

Now I want to make sure NOT to include the rows with customer = 12 in calculating the cost column.

I do not want to Group it y customer. But the query does not let me use Customer column in Having clause if I dont use it in group by.

Select Category, Sum (Cost), Format([OrderDate],"ww")
from OrderTable
GROUP BY Category, Format([OrderDate],"ww")
--HAVING customer <> 12
0
Angel02
Asked:
Angel02
2 Solutions
 
QlemoC++ DeveloperCommented:
Why don't you just use the condition in WHERE?
0
 
Jim Dettman (Microsoft MVP/ EE MVE)PresidentCommented:
HAVING is applied after the grouping so it needs to be in the grouping.   WHERE is applied before, so you want to use WHERE in this case:

Select Category, Sum (Cost), Format([OrderDate],"ww")
from OrderTable
WHERE customer<>12
GROUP BY Category, Format([OrderDate],"ww")

Jim.
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now