Search code examples
sqlitecounthaving-clause

How to count data where sum is greater than x

I am very new to SQL and am using SQLite 3 to run basket analysis on sales data.

The relevant columns are the product ID, a unique transaction ID (which identifies the basket) and the product quantity. Where a customer has bought more than one product type, the unqiue transaction ID is repeated.

I am wanting to count the number of baskets where the customer has bought 1 item.

So far I have tried select count(distinct uniqID) from salesdata having sum(qty) = 1;

But this brought up an error saying a GROUP BY clause is required before HAVING.

I then tried select count(distinct uniqID) from salesdata group by uniqID having sum(qty) = 1

SQlite accepted this, but returned me a list of just 1s, which isn't right either!

I then tried select count(uniqID) from salesdata group by qty having sum(qty) = 1

SQlite also accepted this but returned nothing at all.

Any ideas would be hugely appreciated!

E

Solution

  • Try something like this to retrieve every user which has more or equal than one item in his basket

    select uniqID, sum(qty) as total from salesdata group by uniqID having total >= 1

    if you want to have only the users which have 1 item in their baskets replace >=1 with =1

    like:

    select uniqID, sum(qty) as total from salesdata group by uniqID having total = 1

    If you want the numbers of users with 1 item in their baskets you get this like this:

    SELECT COUNT(*) FROM (select uniqID, sum(qty) as total from salesdata group by uniqID having total 
= 1)