Mysql Question posted in
The official MySQL documentation can be found here.

Mysql – Displaying Date wise values but i want to display values added day by day

I Coded This Code:

SELECT invoice.Date AS Inv_Date, COUNT(*) AS NoOfInv, SUM(Total) AS Inv_SumNetTotal 
FROM invoice 
WHERE invoice.Date BETWEEN DATE_FORMAT(NOW() - INTERVAL 1 MONTH, '%Y-%m-01') AND DATE_FORMAT(LAST_DAY(NOW() - INTERVAL 1 MONTH),'%Y-%m-%d')
GROUP BY invoice.Date
UNION ALL
SELECT  invoice.Date AS Inv_Date,COUNT(*) AS NoOfInv, SUM(Total) AS Inv_SumNetTotal 
FROM invoice 
WHERE invoice.Date BETWEEN DATE_FORMAT(NOW(), '%Y-%m-01') AND DATE_FORMAT(LAST_DAY(NOW()),'%Y-%m-%d')
GROUP BY invoice.Date;

to output the data for line chart

I want to display Inv_SumNetTotal addding one to one day by day

NoOfInv  Inv_Date       Inv_SumNetTotal
104  2024-01-01 **286720**
248  2024-01-02 **720102**
208  2024-01-03 **765290**
231  2024-01-04 820294.6
216  2024-01-05 1032960
185  2024-01-06 643782
206  2024-01-07 483792
205  2024-01-08 678784.5
213  2024-01-09 485897.5
42   2024-01-10 190024
2    2024-02-09 410
2    2024-02-10 9960

this is my output

But I want to display added value like this

NoOfInv  Inv_Date       Inv_SumNetTotal
104  2024-01-01 **286720**
248  2024-01-02 **1,006,822**
208  2024-01-03 **1,772,112**
Tags:

2

Answers


  1. 0

    use this approach

    SELECT
    Inv_Date,
    NoOfInv,
    SUM(Inv_SumNetTotal) OVER (ORDER BY Inv_Date) AS Cumulative_Inv_SumNetTotal
    FROM (
    SELECT
    invoice.Date AS Inv_Date,
    COUNT(*) AS NoOfInv,
    SUM(Total) AS Inv_SumNetTotal
    FROM
    invoice
    WHERE
    invoice.Date BETWEEN DATE_FORMAT(NOW() – INTERVAL 1 MONTH, ‘%Y-%m-01’) AND DATE_FORMAT(LAST_DAY(NOW() – INTERVAL 1 MONTH), ‘%Y-%m-%d’)
    GROUP BY
    invoice.Date

    UNION ALL

    SELECT
    invoice.Date AS Inv_Date,
    COUNT(*) AS NoOfInv,
    SUM(Total) AS Inv_SumNetTotal
    FROM
    invoice
    WHERE
    invoice.Date BETWEEN DATE_FORMAT(NOW(), ‘%Y-%m-01’) AND DATE_FORMAT(LAST_DAY(NOW()), ‘%Y-%m-%d’)
    GROUP BY
    invoice.Date
    ) AS combined_results;

    This query uses the window function SUM(Inv_SumNetTotal) OVER (ORDER BY Inv_Date) to calculate the cumulative sum of Inv_SumNetTotal for each row based on the order of Inv_Date. The result is displayed as Cumulative_Inv_SumNetTotal.

    Now, when you run this query, you should get the desired output with the accumulated values for Inv_SumNetTotal. Adjust the column names or formatting as needed for your specific use case.

    Login or Signup to reply.
  2. 0

    It is not clear why you are using UNION. Your current query is doing (beginning to end of last month) UNION ALL (beginning to end of current month). Surely, beginning of last month to end of current month would make more sense?

    You can use SUM() as a window function to calculate the cumulative sum:

    SELECT
    invoice.Date AS Inv_Date,
    COUNT(*) AS NoOfInv,
    SUM(Total) AS Inv_SumNetTotal,
    SUM(SUM(Total)) OVER (ORDER BY Date) AS Inv_CumSumNetTotal
FROM invoice 
WHERE invoice.Date BETWEEN DATE_FORMAT(NOW() - INTERVAL 1 MONTH, '%Y-%m-01')
                       AND LAST_DAY(NOW())
GROUP BY invoice.Date

    Here’s a db<>fiddle.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search