vr_driver vr_driver - 6 months ago 11
PHP Question

MySQL group by hour and date

I hate to ask a question here, as I know it's been done (slightly), however my problem is slightly different because of the field formats. I've tried a bunch of things on joins and hour selections and others, and I'm stuck. Would love a quick hand.

I've got a MySQL table like so:

CREATE TABLE `VAT` (
`VATid` int(11) NOT NULL AUTO_INCREMENT,
`VATDate` date DEFAULT NULL,
`VATTime` varchar(10) DEFAULT NULL,
`VATPledgeAmount` float DEFAULT NULL,
PRIMARY KEY (`VATid`),
UNIQUE KEY `VATid_UNIQUE` (`VATid`)
) ENGINE=MyISAM AUTO_INCREMENT=6531 DEFAULT CHARSET=latin1;


The problem that is that the VATDate fields have dates in the following format YYYY-MM-DD and the VATTime fields are a HHMM with leading zeros, as shown in the following table with some example data.

Sample Table Data:

VATid | VATDate | VATTime | VATPledgeAmount |
--------------------------------------------------
1 | 2016-06-30 | 0730 | 100 |
2 | 2016-06-30 | 0733 | 20 |
3 | 2016-06-30 | 0840 | 70 |
4 | 2016-06-30 | 0943 | 100 |
5 | 2016-06-30 | 0730 | 50 |
6 | 2016-07-01 | 2113 | 50 |
7 | 2016-07-01 | 2302 | 300 |
8 | 2016-07-02 | 0416 | 10 |
9 | 2016-07-02 | 0417 | 10 |
10 | 2016-07-02 | 0418 | 10 |


What I want to do is have the MySQL server do the calculations for me, so that when the data comes out the dates are still shown, but the total values from the PledgeAmount for each hour are summed up and grouped.

Counts | g_VATDate | g_VATTime | Total_VATPledgeAmount |
--------------------------------------------------
2 | 2016-06-30 | 0700 | 120 |
1 | 2016-06-30 | 0800 | 70 |
1 | 2016-06-30 | 0900 | 100 |
1 | 2016-07-01 | 2100 | 50 |
1 | 2016-07-01 | 2300 | 300 |
3 | 2016-07-02 | 0400 | 30 |


I hope this makes sense. I'm planning on using this for a JS graph, but as I don't need or want the individual pledges and their amount values, I just want the amounts and totals for each hour. Thanks in advance.

Answer

You can use concat(left(VATTime, 2), '00') to get the hour:

select count(VATid) counts, VATDate g_VATDate, 
  concat(left(VATTime, 2), '00') g_VATTime, 
  sum(VATPledgeAmount) Total_VATPledgeAmount
from VAT
group by g_VATDate, g_VATTime;
Comments