Query using group by month

Question

I have problem with the query using group by month. This query returns total_revenue per month. but if month of year doesn't contain any data then total_revnue is increased unnecessarily.

SELECT COUNT(CT.cumTxnReportId),
   CT.cumTxnReportId,
   CT.ticketNum,
   DATE_FORMAT(CT.exitDateTimeUtc,'%m-%Y'),
   sum(netAmount) AS total_revenue,
   D.name,
   HOUR(CT.entranceDateTimeUtc) AS entryHour,
   HOUR(CT.exitDateTimeUtc) AS exitHour,
   CT.entranceDateTimeUtc,
   CT.exitDateTimeUtc,
   CT.netAmount AS netAmount,
   CT.grossAmount,
   CT.discountAmount,
   CT.rate,
   CT.txnType,
   CT.ticketType,
   CT.txnNum,
   CT.numDiscounts
FROM Parkloco.ParkingArea PA
JOIN IParcPro.Device D ON PA.id = D.parkingAreaId
JOIN Parkloco.RateCard RC ON PA.id = RC.parkingAreaId
JOIN IParcPro.CumTxn CT ON D.id = CT.deviceId
WHERE PA.uuid = '27d842c1-7057-11e6-a0eb-1245b0d35d23'
  AND (CT.txnType = 'Allowed'
       OR CT.txnType = 'Add'
       OR CT.txnType = 'Normal'
       OR CT.txnType = 'Offline'
       OR CT.txnType = 'Repay')
  AND ((CT.entranceDateTimeUtc >= '2016-08-01 00:00:00'
        AND CT.exitDateTimeUtc <= '2017-04-31 23:59:59'))
  AND (RC.state = 'active'
       OR RC.state = 'archived')
  AND RC.fromDateTimeUtc <= '2017-04-31 23:59:59'
  AND (RC.thruDateTimeUtc IS NULL
       OR RC.thruDateTimeUtc >= '2016-08-01 00:00:00')
  AND (TIMESTAMPDIFF (SECOND, CT.entranceDateTimeUtc, CT.exitDateTimeUtc) >= '0' * 60)
  AND (TIMESTAMPDIFF (SECOND, CT.entranceDateTimeUtc, CT.exitDateTimeUtc) < '1441' * 60)
  AND CT.numDiscounts=0
  AND CT.ticketNum !=0
GROUP BY DATE_FORMAT(CT.exitDateTimeUtc,'%m-%Y')

but when I am increasing the range month - at that point of time I am getting unneccessary increment in total_revenue

SELECT COUNT(CT.cumTxnReportId),
   CT.cumTxnReportId,
   CT.ticketNum,
   DATE_FORMAT(CT.exitDateTimeUtc,'%m-%Y'),
   sum(netAmount) AS total_revenue,
   D.name,
   HOUR(CT.entranceDateTimeUtc) AS entryHour,
   HOUR(CT.exitDateTimeUtc) AS exitHour,
   CT.entranceDateTimeUtc,
   CT.exitDateTimeUtc,
   CT.netAmount AS netAmount,
   CT.grossAmount,
   CT.discountAmount,
   CT.rate,
   CT.txnType,
   CT.ticketType,
   CT.txnNum,
   CT.numDiscounts
FROM Parkloco.ParkingArea PA
JOIN IParcPro.Device D ON PA.id = D.parkingAreaId
JOIN Parkloco.RateCard RC ON PA.id = RC.parkingAreaId
JOIN IParcPro.CumTxn CT ON D.id = CT.deviceId
WHERE PA.uuid = '27d842c1-7057-11e6-a0eb-1245b0d35d23'
  AND (CT.txnType = 'Allowed'
       OR CT.txnType = 'Add'
       OR CT.txnType = 'Normal'
       OR CT.txnType = 'Offline'
       OR CT.txnType = 'Repay')
  AND ((CT.entranceDateTimeUtc >= '2016-08-01 00:00:00'
        AND CT.exitDateTimeUtc <= '2017-07-31 23:59:59'))
  AND (RC.state = 'active'
       OR RC.state = 'archived')
  AND RC.fromDateTimeUtc <= '2017-07-31 23:59:59'
  AND (RC.thruDateTimeUtc IS NULL
       OR RC.thruDateTimeUtc >= '2016-08-01 00:00:00')
  AND (TIMESTAMPDIFF (SECOND, CT.entranceDateTimeUtc, CT.exitDateTimeUtc) >= '0' * 60)
  AND (TIMESTAMPDIFF (SECOND, CT.entranceDateTimeUtc, CT.exitDateTimeUtc) < '1441' * 60)
  AND CT.numDiscounts=0
  AND CT.ticketNum !=0
GROUP BY DATE_FORMAT(CT.exitDateTimeUtc,'%m-%Y')

output such as :

can anyone help me on this? Thanks in advance if you could let me know.

Answer 1

Despite MySQL allow this weird group by rules, in my opinion, you should to avoid use it. I explain, usually, all select clause non aggregate fields should appear on group by clause:

select a,b,c, sum(z)
from t
group by a,b,c

vs

select a,b,c, sum(z)
from t
group by a   #<--- MySQL allow this!

Then, if b and c are not in group by, how MySQL figure up the right fields to be selected? Like this on <5.6:

The server is free to choose any value from each group, so unless they are the same, the values chosen are indeterminate.

In my opinion, in your query has no sense: Look entryHour and total_revenue. One is for an entry the other one is for all month.

I guess you should to rethink the hole sql statement. Because the result of this one is incoherent.

Also, remember this is not 'code revision service'. Please, read how to create a Minimal, Complete, and Verifiable example in order your question also help other users.