Summarise data by date with percentages using SQL PIVOT

I have some data that looks something like this:

---------------------------------------------
ID |EVENTDATETIME    |CATEGORY |SOMEPROPERTY
---------------------------------------------
1  |20/03/2020 14:30 |CAT A    |1
2  |20/03/2020 16:10 |CAT B    |1
3  |20/03/2020 09:14 |CAT A    |1
4  |20/03/2020 10:26 |CAT A    |0
5  |21/03/2020 11:31 |CAT B    |1
6  |21/03/2020 10:26 |CAT B    |0
7  |22/03/2020 11:31 |CAT A    |1
---------------------------------------------

Essentially I need to (in SQL) pivot this data somehow in to something like this:

---------------------------------------------
DAY |MONTH |YEAR |TOTAL ITEMS |SOMEPROPERTY% 
---------------------------------------------
20  |MARCH |2020 |4           |75%          
21  |MARCH |2020 |2           |50%
22  |MARCH |2020 |1           |100%
---------------------------------------------

Where the SOMEPROPERTY% is the % of the total items that day where the property is set to 1 (it can be 1 or 0).

I may also need to add CATEGORY as an extra level so that the data is grouped by that too:

-------------------------------------------------------
DAY |MONTH |YEAR |CATEGORY |TOTAL ITEMS |SOMEPROPERTY% 
-------------------------------------------------------
20  |MARCH |2020 |CAT A    |3           |66%          
20  |MARCH |2020 |CAT B    |1           |100%          
21  |MARCH |2020 |CAT B    |2           |50%
22  |MARCH |2020 |CAT A    |1           |100%
-------------------------------------------------------

Can anyone assist?

Needs to be a single SQL statement – I tried doing it using GROUP BY but it got quite messy and was giving odd results. I think it may be possible with PIVOT but I can’t find a good example and it’s not the easiest command to play with.

n.b. I know that this sort of stuff would normally be done in the presentation layer rather than directly in SQL but I don’t have that option for where this is needed.

Thanks.

Answer

Use conditional aggregation. I’m going to assume SQL Server for this purpose:

select convert(date, eventdatetime) as dte, category,
       count(*) as total_items,
       avg( someproperty * 1.0 ) as ratio
from t
group by convert(date, eventdatetime), category;

There is no need to put year/month/day in separate columns.

The equivalent function in Oracle would be trunc(eventdatetime). There is similar functionality in whatever database you are using.

Advertisement

Answer