Aggregating functions in SQL queries

Question

Aggregating functions in SQL queries

Good afternoon.

In the MS SQL Server 2008 database, you need to run a query that contains the COUNT() function and filters by the Chanel column.

SELECT     TOP (100) PERCENT SR, COUNT(Route_SR) AS Work_base, Chanel
FROM         dbo.Sensus
GROUP BY SR, Route_SR, Chanel
HAVING      (Route_SR = 'да') AND (Chanel = 'маленький магазин' OR
                      Chanel = 'большой магазин')
ORDER BY SR

The problem is that this query displays the number of records in the context of SR and Chanel. I only need to check the two columns SR and COUNT (Route_SR), but by filtering (Chanel = 'small store' OR Chanel = 'large store').

Request:

SELECT     TOP (100) PERCENT SR, COUNT(Route_SR) AS Work_base 
FROM         dbo.Sensus
GROUP BY SR, Route_SR
HAVING      (Route_SR = 'да') AND (Chanel = 'маленький магазин' OR
                      Chanel = 'большой магазин')
ORDER BY SR

Returns an error, because Chanel is not contained in GROUP BY.

How to get the required data using SQL?

2

sql-server sql

Author: PashaPash, 2011-08-03

Source

1 answers

score 6 · Answer 1

The AND (Chanel = 'маленький магазин' OR Chanel = 'большой магазин') condition should not be used in HAVING, but in WHERE. In general, all the conditions should be moved to WHERE. HAVING in general, it is fundamentally different in that it works with aggregated data, why use it as a selection criterion?

SELECT TOP (100) PERCENT SR, COUNT(Route_SR) AS Work_base 
FROM dbo.Sensus  
WHERE (Route_SR = 'да') AND (Chanel = 'маленький магазин' OR Chanel = 'большой магазин') 
GROUP BY SR, Route_SR 
ORDER BY SR