group by on a column not present in having clause

Question

I have a simple SQL query with GROUP BY , HAVING and ORDER BY clauses. The HAVING clause has some fields that I don't want in the GROUP BY , is that possible ? Because I have to group the data hourly , and I have a date field in the HAVING , so the grouping doesn't work properly. The code is:

alter procedure [dbo].[sp_metadata_inflow]
@grp_name varchar(40) , @subgrp_name varchar(40)
as
begin
declare @i as int , @j as int,@k as int,@d as datetime , @m as datetime,@y as datetime;
set @d = datepart(day,'2012-12-13 10:54:55.000');
set @m = datepart(month,'2012-12-13 10:54:55.000');
set @y = datepart(YEAR,'2012-12-13 10:54:55.000');
set @i = 1;
set @j = @i - 1;
while (@i <=24)
begin
(SELECT  top 5 @i,
         ContactReason,
         @grp_name,
         @subgrp_name,
         COUNT(*)
 FROM   [ISRM].[dbo].[ITSM01ISRM]
 GROUP  BY ContactReason,datepart(day,CreatedDate),CurrentGroup,CurrentSubGroup
 HAVING (
         datepart(day,CreatedDate)= @d AND datepart(MONTH,CreatedDate)= @m
         AND datepart(year,CreatedDate) = @y AND datepart(hour,CreatedDate) <= @i
         AND datepart(hour,CreatedDate) >=@j
        )
        AND (CurrentGroup = @grp_name) AND (CurrentSubGroup = @subgrp_name)
);
set @i = @i + 1;
set @j =  @i -1;
end
end
go

Can you explain your query? I mean what you want to do using query in actual? — Dhwani, Feb 27 '13 at 07:10
So , I want to get the count(*) based on Contact Reason(group by) , for every hour of the day... Hope that makes sense, My only uqestion is how do i get it without having to write CreatedDate in group by clause — user2042493, Feb 27 '13 at 07:14
It is possible in MySQL (though not standard SQL) to have columns in the HAVING clause that are not in the GROUP BY. See http://dev.mysql.com/doc/refman/4.1/en/group-by-hidden-columns.html — GreyBeardedGeek, Feb 27 '13 at 07:17
Which DBMS are you using? Asking this because there is no DATEPART function in MySQL. [Check this post](http://stackoverflow.com/questions/13402012/select-row-by-datepart) — araknoid, Feb 27 '13 at 07:22

score 0 · Answer 1 · answered Feb 27 '13 at 09:52

The following query groups all results by hour. Why don't you want to have the CreatedDate in the GROUP BY?

SELECT
    ContactReason,
    CurrentGroup,
    CurrentSubGroup,
    dateadd(hour, datediff(hour, 0, CreatedDate), 0),
    COUNT(1) 
FROM [ISRM].[dbo].[ITSM01ISRM] 
WHERE
    (CurrentGroup = @grp_name) AND (CurrentSubGroup = @subgrp_name)
GROUP BY 
    ContactReason,
    dateadd(hour, datediff(hour, 0, CreatedDate), 0),
    CurrentGroup,
    CurrentSubGroup;

score 0 · Answer 2 · answered Feb 27 '13 at 10:18

To actually answer your question, It looks like you just want these conditions in the where clause. It also looks very much like SQL-Server syntax, not MySQL as the tags suggest.

It is also worth noting that this is very inefficient:

    (datepart(day,CreatedDate)= @d 
AND datepart(MONTH,CreatedDate)= @m
AND datepart(year,CreatedDate) = @y 
AND datepart(hour,CreatedDate) <= @i 
AND datepart(hour,CreatedDate) >=@j )

using DATEPART on the column will not only force the function to evaluate for every row, it also removes the benefits of any index on the column. This would be much better written as Createddate >= '20130227 00:00' AND CreatedDate < '20130227 01:00'.

In addition in each loop you executing for 2 hour periods which does not sound like the intended behaviour.

Finally I cannot imagine returning 24 results sets is the best way to deal with the data, If you want a count for each hour would it not make sense to have the hours as columns and execute one query and return one dataset?

e.g.

| ContactReason  | GroupName  | SubGroupName | 00:00 | 01:00 | 02:00 | 03:00 |.....| 23:00 |
|----------------+------------+--------------+-------+-------+-------+-------+.....+-------|
| Example Reason | Test Group | Sub Group    |   5   |   10  |    8  |    1  |.....|   14  |
| Another Reason | Test Group | Sub Group    |   3   |    1  |   13  |    8  |.....|   23  |

In which case your query could be written as

DECLARE @Date DATETIME = '20121213';

WITH Data AS
(       SELECT  ContactReason,
                GroupName = @grp_name,
                SubGroupName = @subgrp_name,
                CreatedHour = CAST(DATEADD(HOUR, DATEDIFF(HOUR, 0, Createddate), 0) AS TIME),
                [Value] = 1
        FROM    [ISRM].[dbo].[ITSM01ISRM] 
        WHERE   CurrentGroup = @grp_name
        AND     CurrentSubGroup = @subgrp_name
        AND     CreatedDate >= @Date
        AND     CreatedDate < DATEADD(DAY, 1, @Date)
)
SELECT  *
FROM    Data
        PIVOT
        (   COUNT(Value)
            FOR CreatedHour IN 
                (   [00:00], [01:00], [02:00], [03:00], [04:00], [05:00], 
                    [06:00], [07:00], [08:00], [09:00], [10:00], [11:00], 
                    [12:00], [13:00], [14:00], [15:00], [16:00], [17:00],
                    [18:00], [19:00], [20:00], [21:00], [22:00], [23:00]
        ) pvt;

group by on a column not present in having clause

2 Answers2