SQL - Counting with Where

Question

I have a very simple SQL question. I have a database table with the following columns:

Part Number
Sales Type (for simplicity, call it Sales Type 1,2,3,4,5)

I am hoping to write a query that gives the following three columns:

The part number
The total number of sales for Sales Type = 2
The total number of sales for Sales Type <> 2

I can easily get two of the three columns to display with code similar to the code below but I'm stumped on how to get all three to show up at the same time.

SELECT 
    PartNumber AS PartNumber,
    COUNT(*) AS SalesCount
FROM 
    SalesTable
WHERE 
    SalesType = 2
GROUP BY 
    I.PartNumber

I'm guessing this may be very easy - I'm a SQL noob and this is my first post!

Any help is greatly appreciated!

There is no `and` in select statement. So had you tested your query? — Lee, Nov 29 '17 at 04:28
Just edited it should be "AS". I simplified the example for this question so I couldn't test my code exactly. Do you know how to display all three columns as I'm hoping for? — Zak Fischer, Nov 29 '17 at 04:30
If I had GROUP BY it will not be three columns though right? — Zak Fischer, Nov 29 '17 at 04:31
The COUNT doesn't work without GROUP BY (except under MySQL where it also doesn't work, but you don't see it as explicitly). — Branko Dimitrijevic, Nov 29 '17 at 04:33

score 2 · Answer 1 · answered Nov 29 '17 at 04:37

It sounds like what you're looking for is a conditional count

SELECT 
    PartNumber, 
    SUM(CASE WHEN SalesType = 2 THEN 1 ELSE 0 END) as SaleTypeTwoCount,
    SUM(CASE WHEN SalesType <> 2 THEN 1 ELSE 0 END) as SalesOtherCount
FROM 
    SalesTable
GROUP BY
    PartNumber

score 1 · Accepted Answer · answered Nov 29 '17 at 04:32

1

Untested, but you want something like this:

 SELECT PartNumber, sum(case when SalesType=2 then 1 else 0) as Type2SalesCount,
    sum(case when SalesType<>2 then 1 else 0) as NoNType2SalesCount
    FROM SalesTable
    Group by PartNumber

answered Nov 29 '17 at 04:32

user8834780

1,620
3
21
48

Perfect - thanks!!! This did it (I did have to add an END ... else 0 END). But this was exactly what I was looking for --- thank you so much!! – Zak Fischer Nov 29 '17 at 04:39

score 0 · Answer 3 · answered Nov 29 '17 at 04:41

Haven't actually run it, but something like this should work...

SELECT
    PartNumber,
    COUNT(CASE WHEN SalesType = 2 THEN 1 END) Count2,
    COUNT(CASE WHEN SalesType <> 2 THEN 1 END) CountNot2
FROM
   SalesTable
GROUP BY
   PartNumber;

Note that COUNT(expression) only counts non-NULLs, while COUNT(*) counts all rows.

The...

CASE WHEN SalesType = 2 THEN 1 END

...is equivalent to...

CASE WHEN SalesType = 2 THEN 1 ELSE NULL END

...and will result in NULL when SalesType <> 2, which is not counted.

score 0 · Answer 4 · answered Nov 29 '17 at 04:42

0

You can use case for example: saletype= case when saletype == 2 then count(saletype) when saletype <> 2 then count(saletype)

answered Nov 29 '17 at 04:42

Lupitha Yañez C.

354
1
2
7

SQL - Counting with Where

4 Answers4