Group By not grouping properly

Question

I work with oracle, and the group by command seems to behave very differently than I expected.

When using this query:

SELECT stats.gds_id, stats.stat_date, SUM(stats.A_BOOKINGS_NBR) as "Bookings", SUM(stats.RESPONSES_LESS_1_NBR) as "<1", SUM(stats.RESPONSES_LESS_2_NBR) AS "<2", SUM(STATS.RESPONSES_LESS_3_NBR) AS "<3", SUM(stats.RESPONSES_LESS_4_NBR) AS "<4", SUM(stats.RESPONSES_LESS_5_NBR) AS "<5", SUM(stats.RESPONSES_LESS_6_NBR + stats.RESPONSES_LESS_7_NBR + stats.RESPONSES_GREATER_7_NBR) AS ">5", SUM(stats.RESPONSES_LESS_6_NBR) AS "<6", SUM(stats.RESPONSES_LESS_7_NBR) AS "<7", SUM(stats.RESPONSES_GREATER_7_NBR) AS ">7", SUM(stats.RESPONSES_LESS_1_NBR + stats.RESPONSES_LESS_2_NBR + stats.RESPONSES_LESS_3_NBR + stats.RESPONSES_LESS_4_NBR + stats.RESPONSES_LESS_5_NBR + stats.RESPONSES_LESS_6_NBR + stats.RESPONSES_LESS_7_NBR + stats.RESPONSES_GREATER_7_NBR) as "Total" FROM gwydb.statistics stats WHERE stats.stat_date >= '01-JUN-2011' GROUP BY stats.gds_id, stats.stat_date 

I get the following results:

 GDS_ID STAT_DATE Bookings <1 <2 <3 <4 <5 >5 <6 <7 >7 Total 02 12-JUN-11 0 1 0 0 0 0 0 0 0 0 1 1A 01-JUN-11 15 831 52 6 2 2 4 1 1 2 897 1A 01-JUN-11 15 758 59 8 1 1 5 2 1 2 832 1A 01-JUN-11 10 593 40 2 2 1 2 1 0 1 640 1A 01-JUN-11 12 678 40 10 5 2 3 1 0 2 738 1A 01-JUN-11 24 612 56 6 1 3 4 0 0 4 682 1A 01-JUN-11 23 552 37 7 1 1 2 0 1 1 600 1A 01-JUN-11 35 1147 132 13 6 0 8 0 2 6 1306 1A 01-JUN-11 91 2331 114 14 5 1 14 3 1 10 2479 

As you can see, I have several duplicates of STAT_DATE for GDS_ID. Why is this and how can I make this a group? I.E. Sum the values ​​for each GDS_ID at STAT_DATE.

Answer 1

Probably because STAT_DATE has a time component that is taken into account in GROUP BY but not displayed in the results due to the default format mask. To ignore time, do the following:

 SELECT stats.gds_id, TRUNC(stats.stat_date) stat_date, SUM(stats.A_BOOKINGS_NBR) as "Bookings", SUM(stats.RESPONSES_LESS_1_NBR) as "<1", SUM(stats.RESPONSES_LESS_2_NBR) AS "<2", SUM(STATS.RESPONSES_LESS_3_NBR) AS "<3", SUM(stats.RESPONSES_LESS_4_NBR) AS "<4", SUM(stats.RESPONSES_LESS_5_NBR) AS "<5", SUM(stats.RESPONSES_LESS_6_NBR + stats.RESPONSES_LESS_7_NBR + stats.RESPONSES_GREATER_7_NBR) AS ">5", SUM(stats.RESPONSES_LESS_6_NBR) AS "<6", SUM(stats.RESPONSES_LESS_7_NBR) AS "<7", SUM(stats.RESPONSES_GREATER_7_NBR) AS ">7", SUM(stats.RESPONSES_LESS_1_NBR + stats.RESPONSES_LESS_2_NBR + stats.RESPONSES_LESS_3_NBR + stats.RESPONSES_LESS_4_NBR + stats.RESPONSES_LESS_5_NBR + stats.RESPONSES_LESS_6_NBR + stats.RESPONSES_LESS_7_NBR + stats.RESPONSES_GREATER_7_NBR) as "Total" FROM gwydb.statistics stats WHERE stats.stat_date >= '01-JUN-2011' GROUP BY stats.gds_id, TRUNC(stats.stat_date) stats.RESPONSES_LESS_4_NBR + stats.RESPONSES_LESS_5_NBR + stats.RESPONSES_LESS_6_NBR + stats.RESPONSES_LESS_7_NBR + stats.RESPONSES_GREATER_7_NBR) as "Total" SELECT stats.gds_id, TRUNC(stats.stat_date) stat_date, SUM(stats.A_BOOKINGS_NBR) as "Bookings", SUM(stats.RESPONSES_LESS_1_NBR) as "<1", SUM(stats.RESPONSES_LESS_2_NBR) AS "<2", SUM(STATS.RESPONSES_LESS_3_NBR) AS "<3", SUM(stats.RESPONSES_LESS_4_NBR) AS "<4", SUM(stats.RESPONSES_LESS_5_NBR) AS "<5", SUM(stats.RESPONSES_LESS_6_NBR + stats.RESPONSES_LESS_7_NBR + stats.RESPONSES_GREATER_7_NBR) AS ">5", SUM(stats.RESPONSES_LESS_6_NBR) AS "<6", SUM(stats.RESPONSES_LESS_7_NBR) AS "<7", SUM(stats.RESPONSES_GREATER_7_NBR) AS ">7", SUM(stats.RESPONSES_LESS_1_NBR + stats.RESPONSES_LESS_2_NBR + stats.RESPONSES_LESS_3_NBR + stats.RESPONSES_LESS_4_NBR + stats.RESPONSES_LESS_5_NBR + stats.RESPONSES_LESS_6_NBR + stats.RESPONSES_LESS_7_NBR + stats.RESPONSES_GREATER_7_NBR) as "Total" FROM gwydb.statistics stats WHERE stats.stat_date >= '01-JUN-2011' GROUP BY stats.gds_id, TRUNC(stats.stat_date)