Is it possible to select case sums into the same column for different sums?-CodePudding

Given a table "items"

id, timestamp, type
1, 1, "ONE"
1, 5, "ONE" 
1, 10, "ONE" 
1, 1, "TWO" 
1, 5, "TWO" 
1, 10, "TWO"

I would like to get the total count, and the total count for each group greater than some date. However the date can be different for each type.

I have this:

SELECT type,
    count(*) AS total,
    sum(case when type = "ONE" AND timstamp > 4 then 1 else 0 end) AS one_count,
    sum(case when type = "TWO" AND  timestamp > 7 then 1 else 0 end) AS two_count
FROM items GROUP BY type

However that returns 4 columns; type, total, one_count, two_count.

Query above will return

type, total, one_count, two_count
ONE, 3, 2 0
TWO, 3, 0 1

Based on the grouping, only a single "count" column will have a value (other than total).

How could I query with results in a single "count" column, ie results:

type, total, greater_than_date_count
ONE, 3, 2
TWO, 3, 1

CodePudding user response：

Use conditional aggregation like this:

SELECT type,
       COUNT(*) AS total,
       COUNT(CASE WHEN timestamp > CASE type WHEN 'ONE' THEN 4 WHEN 'TWO' THEN 7 END THEN 1 END) AS count
FROM items 
GROUP BY type;

See the demo.

CodePudding user response：

You join vertically with UNION

SELECT type,
    count(*) AS total,
    'ONE' as type,
    sum(case when type = "ONE" AND timstamp > 1655047560000 then 1 else 0 end) AS count
FROM items GROUP BY type
UNION
SELECT type,
    count(*) AS total,
    'TWO'
    sum(case when type = "TWO" AND  timestamp > 1655047110000 then 1 else 0 end) AS count
FROM items GROUP BY type