Menu
I have the following query:
select count(ords.TRACKING_NUM)
from Orders ords (NoLock)
group by ords.TRACKING_NUM
having count(distinct ords.ORDER_NUM) = 4
I want it to retrieve the total amount of TRACKING_NUMs that have 4 ORDER_NUMs on them (should be 3,352). Instead I get 3,352 rows equal to 4 (or more because of the distinct).
I get why this is happening. It is counting the values inside each group by. And I can easily change the query to this:
select ords.TRACKING_NUM
from Orders ords (NoLock)
group by ords.TRACKING_NUM
having count(distinct ords.ORDER_NUM) = 4
and then it get 3,352 rows of TRACKING_NUMs returned to me. However, this is not very performant on my database (takes about 41 seconds). What I really need is a query that will give me a count and only a count (and by doing that hopefully go faster).
Thanks for any suggestions.
311
To count the number of rows, use the id column which stores unique values (in our example we use COUNT(id) ). Next, use the GROUP BY clause to group records according to columns (the GROUP BY category above). After using GROUP BY to filter records with aggregate functions like COUNT, use the HAVING clause.
The GROUP BY statement is often used with aggregate functions ( COUNT() , MAX() , MIN() , SUM() , AVG() ) to group the result-set by one or more columns.
SELECT DISTINCT ColumnName FROM TableName; Using the COUNT() function with the GROUP BY clause, then the query will produce the number of values as the count for each subgroup created based on the table column values or expressions.
The SQL COUNT() function returns the number of rows in a table satisfying the criteria specified in the WHERE clause. It sets the number of rows or non NULL column values. COUNT() returns 0 if there were no matching rows.
SELECT COUNT(*)
FROM (
SELECT TRACKING_NUM
FROM Orders
GROUP BY TRACKING_NUM
HAVING count(distinct ORDER_NUM) = 4) AS Agg
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
