mysql aggregate multiple columns by hour.

Question

Hi all,

Had a quick look through and couldn't find my question again. So I'm hoping someone here can help. I have a large table with a similar structure to the below.

DateTime | InboundUserID | OutboundUserID | CustomerUserID | Cost | Mins | Account Number

I'm trying to group the above data into hourly chunks and store the result in another table with a similar structure to below.

Date | HourFrom | HourTo | SumMins | SumCost | OutboundUserID | CustomerUserID | Account Number

I've written the following code / query in order to do this but it's not quite right. Basically the group by means not all the data gets summarised. For instance when looking at the newly created table I have all the OutboundUserID data, but none of the CustomerUserID data.

    for(int i=0;i <=23;i++)
    {
        for(int j=1 ; j <=31 ; j++)
        {
            String Time = String.format("%02d", i);

            query = "INSERT DELAYED INTO correlated_calls_hourly " +
                    " SELECT DateOnly,HourFrom,HourTo,DURATION,CALLCOUNT,`Price Group ID`,INBOUNDCOST,INBOUNDREVENUE,`InboundCustomerID`,OUTBOUNDCOST,OUTBOUNDREVENUE,`Outbound Customer ID`,`Customer ID`,`Account Number`,`Service` FROM" +
                    " ("+
                        " SELECT "+
                        /* snip sum(),case and date/time part of query */
                        " GROUP BY  DateOnly,`InboundCustomerID`,`OutboundCustomerID`,`CustomerID`,`AccountNumber`,`PriceGroupID`,`Service`" +
                    " )a ";
        }
    }

I've already taken the step of creating three different tables to summarise the data separately by OutboundUserID/ InboundUserID / CustomerUserID. But it would be a lot more elegant if I could just get this one summarised table working correctly.

If anyone could help me think of a way to get the desired result via a single sql query I'd be greatly appreciative.

Many thanks in advance

Alan

Answer 1

Would something like this work?

INSERT INTO correlated_calls_hourly (Date, HourFrom, HourTo, SumMins, SumCost, OutboundUserID, CustomerUserID, AccountNumber)
SELECT 
  CONVERT(varchar(8), GETDATE(), 112),
  hour(DateTime),
  hour(DateTime) + 1, 
  SUM(Mins),
  SUM(Cost),
  OutboundUserID,
  CustomerUserID,
  AccountNumber
GROUP BY
  CONVERT(varchar(8), GETDATE(), 112),
  hour(DateTime)
  OutboundUserID,
  CustomerUserID,
  AccountNumber

The convert function is convert the datetime to just a date. You'd probably need to tweak it a little to suit your RDBMS but I can't see any reason why that wouldn't work.

Answer 2

After a little inspiration I realised the solution was obvious. Concat the columns together and do a group by on the concatenated column.

CONCAT(InboundUserID,'',OutboundUserID,'',CustomerUserID) as uniqueKey GROUP BY uniqueKey.