SQL Aggregate Function Grouping with WHERE Clause-CodePudding

I have a SQL table that has data like the following:

shift	event
shift 1	0
shift 1	1
shift 2	0

What I need to do is get a returned aggregate grouped by shift that displays both the total number of occurrences of the shift, and the number of events that occurred for the shift. Something like this:

shift 1 - 2 - 1
shift 2 - 1 - 0

The query I have tried most recently is as follows:

SELECT 
    shift,
    COUNT(shift) AS totalShifts,
    (SELECT COUNT(shift) FROM table WHERE event = 1) AS totalEvents
FROM table
GROUP BY shift

This query successfully returns the total number of shifts for each unique shift but keeps returning total events = 1 not independent of shift for each shift:

shift 1 - 2 - 1
shift 2 - 1 - 1

I have tried adding additional group by's and partitioning the table data but I always get this same result. What do I need to add to get this to work as desired?

CodePudding user response：

Something like this should work:

SELECT
    shift,
    COUNT(1) As totalShifts,
    SUM(CAST(event As int)) As totalEvents
FROM
    table
GROUP BY
    shift

DB fiddle

Your current query is counting all "event 1" rows for every row in the resultset, regardless of the shift.

CodePudding user response：

You can use aggregate functions Count and Sum with Concat function.

Select Concat([shift],' - ', Count(*),' - ', Sum(Convert(int,[event])))
From [Table]
Group by [shift]
Order by [shift]