SQL - Using Count with WHERE clause

Question

I'm trying to do a simple, at first sight, SQL query, but I can't wrap my head around what I'm doing wrong.

Problem

Imagine these are my tables:

TableUsers

UserID      FirstName   LastName
1           Bill        Johnson
2           Alex        Agnew
3           Mike        Owen
4           Kate        Ryan

TableArticles

ArticleID   Description
1           Bananas
2           Eggs
3           Milk

TableOrders

OrderID     UserID      ArticleID
1           1           1
2           1           2
3           2           3
4           2           2
5           3           3

I want to list all users who have at least one order linked to their name, count how many orders and count how many of a specific article they ordered:

Desired result

UserID  FirstName   # Orders    # Banana orders     # Egg orders
1       Bill            2           1               1
2       Alex            2           0               1
3       Mike            1           0               0

I have tried:

SELECT
    UserID,
    FirstName,
    COUNT(*) AS '# Orders',
    (SELECT COUNT(*) FROM TableOrders O WHERE O.UserID = TableOrders.UserID AND O.ArticleID = 1) AS '# Banana orders',
    (SELECT COUNT(*) FROM TableOrders O WHERE O.UserID = TableOrders.UserID AND O.ArticleID = 2) AS '# Egg orders',
FROM TableUsers
LEFT JOIN TableOrders ON TableOrders.UserID = TableUsers.UserID
GROUP BY UserID, FirstName
HAVING Count(*) > 0;

But I'm getting an error saying the ArticleID needs to be in an aggregate function or group by clause. If I add the ArticleID to the group by clause, users with multiple orders are shown more than once...

Any help is appreciated!

Thank you!

HAVING Count(*) > 0 doesn't reject any rows at all.

jarlh
– jarlh

2020-11-16 11:48:49 +00:00
Commented Nov 16, 2020 at 11:48 — jarlh
– jarlh, Commented Nov 16, 2020 at 11:48

Gordon Linoff · Accepted Answer · 2020-11-16 11:33:56Z

1

Just use conditional aggregation:

SELECT o.UserID, o.FirstName, COUNT(*) AS num_orders,
       SUM(CASE WHEN o.ArticleID = 1 THEN 1 ELSE 0 END) as num_bananas, 
       SUM(CASE WHEN o.ArticleID = 2 THEN 1 ELSE 0 END) as num_eggs 
FROM TableUsers u JOIN
     TableOrders o
     ON o.UserID = u.UserID
GROUP BY UserID, FirstName;

Notes:

You want users with orders, so just use an inner join, not an outer join.
The HAVING clause is then not necessary.
Table aliases make the query easier to read and write.
Don't use single quotes for column aliases. It is better to name the columns so they don't need to be escaped.

answered Nov 16, 2020 at 11:33

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

SQL - Using Count with WHERE clause

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related