SQL get number of items with sales over certain quantity

Admin 1
Viewed 127

Using SQL Server

I have a database table which tracks all the transactions in a store.

The following query gives me a list of item IDs where the items have sales over a certain quantity:

select
    ItemID
    , SUM(Quantity)
from
     TransactionEntry 
group by
        ItemID
Having 
    Sum(Quantity) >3500

There are 5 such items:

ItemID (No column name)
7543 4558
6484 7242
6482 4145
6617 3931
6486 4499

However, I cannot for the life of me figure out how to get simply 5 - that is, how many items have sales of more than 3500 units? I know this is something simple but I cannot see it. If I try:

select
    Count(ItemID)
from
     TransactionEntry 
Having 
    Sum(Quantity) >3500

I get an answer - 241010! Very definitely not 5!!

Please show what I'm doing wrong!

tsql sql-server sql
2 Answers

You can use a subquery:

select count(*)
from (select ItemID, SUM(Quantity)
      from TransactionEntry 
      group by ItemID
      having Sum(Quantity) > 3500
     ) i;
user_11440350

Another way of achieving the same would be

select Sum(over3500) from (
    select Iif(Sum(Quantity)>3500,1,0) over3500
    from TransactionEntry 
    group by ItemID
)s
user_153326500
Related