Home > Blockchain >  Sum up column between two dates, group by 3 columns - MySQL
Sum up column between two dates, group by 3 columns - MySQL

Time:07-19

Can someone please suggest a way to sum up the "count" column between two dates and group it according to the "mcc", "partner" and "result" columns.

"Stats" table:

id date count mcc partner result
1 2022-07-13 4 293 TynTec SUCCESS
2 2022-07-13 5 293 TynTec FATAL
3 2022-07-13 4 294 NTH SUCCESS
4 2022-07-14 9 294 NTH SUCCESS
5 2022-07-14 4 294 TynTec SUCCESS
6 2022-07-14 10 294 NTH FATAL

Desired output:

id date count mcc partner result
1 2022-07-13 - 2022-07-14 8 293 TynTec SUCCESS
2 2022-07-13 - 2022-07-14 5 293 TynTec FATAL
3 2022-07-13 - 2022-07-14 13 294 NTH SUCCESS
4 2022-07-13 - 2022-07-14 10 294 NTH FATAL

Thank you.

CodePudding user response:

Hope this will helps you.

   select concat(mindv , ' - ' , maxdv) as dates, 
          sum(cnt) as counts,
          min(mcc) as mcc,
          partner,
          res as result 
   from tmp_1,
   (select min(dt) as mindv ,partner as pmin from tmp_1 group by 2) mind,
   (select max(dt) as maxdv , partner as pmax  from tmp_1 group by 2) maxd
   where 
      mind.pmin=maxd.pmax
      and partner=mind.pmin
      group by 1,4,5;

CodePudding user response:

This piece of code returns the result that you want; It didn't optimize but gives you the idea of what you have to do:

SELECT  
    CONVERT(char(10), T.[date],126)  
    ' - '
      CONVERT(char(10), DATEADD(DAY,IIF(DATEDIFF(DAY, T.[date], NextDate) 
IS NOT NULL, DATEDIFF(DAY, T.[date], NextDate),1),T.[date]),126),
    T.ItemCount
    T.mcc,
    T.[partner],
    T.[result]
FROM    (SELECT T1.*,(SELECT  MIN([date]) FROM (SELECT 
            SUM([count]) AS ItemCount,
            [mcc], 
            [partner], 
            [result],
            DATEADD(DAY,0, datediff(day,0, [date])) AS [date]
        FROM [TABLENAME] 
        WHERE [date] between '[FromDate]' and '[ToDate]'
        GROUP BY [mcc], [partner], [result], dateadd(DAY,0, DATEDIFF(DAY,0, 
  [date]))) AS T2
                    WHERE   T2.mcc = T1.mcc AND T2.[partner]=T1.[partner] 
AND T2.[result]=T1.[result]
                    AND     T2.[date] > T1.[date]
                ) AS NextDate
FROM (SELECT 
            SUM([count]) AS ItemCount,
            [mcc], 
            [partner], 
            [result],
            DATEADD(DAY,0, DATEDIFF(day,0, [date])) AS [date]
        FROM [TABLENAME] 
        WHERE [date] between '[FromDate]' and '[ToDate]'
        GROUP BY [mcc], [partner], [result] , DATEADD(DAY,0, 
DATEDIFF(DAY,0, [date]))) AS T1 )AS T

To describe a quick and short senario about the code :

  1. I generate a query that GROUP BY columns you said and the date.
  2. Then I get NextDate of SELECT Statement to generate Time Period
  3. In final step I select the columns that you want from table and generate Time Period related to Date and NextDate

note: If NextDate column be null generated date automatically add one day to Time Period.

If you have any questions write them below in the comments.

  • Related