PL/SQL Aggregate Woes

Neon_Lights

Hi,

I've been having a bit of a mind melt in work over this query, i just cant get it to aggregate the way I want to. I've tried, TRUNC, SUM, TO_CHAR, TO_DATE havent nested it yet but would like to see if there is a simpler way.

Essentially i want it broken down by date, and all fields in the underlying table are in a date time format, so they come up with multiple values for the same date and type.

The query in question is below: it should return three columns
Type, Date, Count (*) broken down by date:

This is what i have at the moment and I've anonymised the query, any help would be appreciated.

SELECT tb.type " Type",TRUNC(TO_DATE(tb.datetime, 'dd/mm/yyyy'), 'dd/mm/yyyy'), Count(*)

FROM table.tab tb

WHERE tb.type in ('Type1','Type2','Type3')
AND tb.datetime > TO_DATE('05/10/2015','dd/mm/yyyy')
GROUP BY tb.type, tb.datetime;

Zith

Try something like this:

SELECT tb.type " Type",TRUNC(TO_DATE(tb.datetime, 'dd/mm/yyyy'), 'dd/mm/yyyy'), Count(*)
FROM table.tab tb

WHERE tb.type in ('Type1','Type2','Type3')
AND tb.datetime > TO_DATE('05/10/2015','dd/mm/yyyy')
GROUP BY tb.type, TRUNC(TO_DATE(tb.datetime, 'dd/mm/yyyy');

You need to group on exactly what you expect the output to be. By grouping on the tb.datetime you still include seconds hence it doesn't group as you want.

Neon_Lights

D'oh!

Thanks Zith, had an aul play around with the Query and got it grouped using TO_DATE() in the group by function.

Appreciate the help

Zith

Happy to help!