Geek asks and answers

Why doesn't this query change when the DISTINCT keyword is used?

Why is the result of two queries the same? (I am using the Northwind database.)

SELECT      ContactTitle
        ,   COUNT(CustomerID) AS NumberOfCustomer
FROM        dbo.Customers
WHERE       ContactTitle LIKE '%sales%'
GROUP BY    ContactTitle
HAVING      COUNT(*) >= 5
ORDER BY    NumberOfCustomer desc

and

SELECT 
DISTINCT    ContactTitle
        ,   COUNT(CustomerID) AS NumberOfCustomer
FROM        dbo.Customers
WHERE       ContactTitle LIKE '%sales%'
GROUP BY    ContactTitle
HAVING      COUNT(*) >= 5
ORDER BY    NumberOfCustomer desc

result:

ContactTitle           NumberOfCustomer
---------------------  ----------------
Sales Representative         17
Sales Manager                11
Sales Associate               7
Sales Agent                   5

In my own understanding, the second query receives separate headers and counts its records, so I expect the result to be nobody, because each header has only the number of records 1. Am I right?

+3
source share
4 answers

This is how query execution works. In your second expression, it does not perform any additional function, because yours , containing the same column name , has already performed this operation for you, DISTINCT GROUP BYContactTitle

1. FROM
2. WHERE
3. GROUP BY <-- You have specified the column `ContactTitle`, 
-- which means the results would be grouped by that column to product unique 
--result.
4. HAVING
5. SELECT <-- Adding DISTINCT on ContactTitle column here doesn't make much 
-- difference and it is actually redundant. DISTINCT is applied to the whole
-- row but the resultset already contains distinct rows grouped by the column 
-- `ContactTitle`.
6. ORDER BY
+3
source

DISTINCT . GROUP BY, , DISTINCT .

+7

Differences apply to heading and invoice. After your choice is completed, it then creates an excellent list from it.

+3
source

DISTINCTwill filter duplicate records from the result set. Since in this case there are no duplicate entries, DISTINCTit has no effect.

+3
source

More articles:


All Articles