Sum datas by date with MySql

Question

I'm trying to make a report of financial datas for my company:

My table is ___BillableDatas and looks like this:

|--------|------------|----------|----------|--------------|
| BIL_Id | BIL_Date   | BIL_Type | BIL_Rate | BIL_Quantity |
|--------|------------|----------|----------|--------------|
| 1      | 2017-01-01 | Night    | 95       | 1            |
| 2      | 2017-01-02 | Night    | 95       | 1            |
| 3      | 2017-01-15 | Night    | 105      | 1            |
| 4      | 2017-01-15 | Item     | 8        | 2            |
| 5      | 2017-02-14 | Night    | 95       | 1            |
| 6      | 2017-02-15 | Night    | 95       | 1            |
| 7      | 2017-02-16 | Night    | 95       | 1            |
| 8      | 2017-03-20 | Night    | 89       | 1            |
| 9      | 2017-03-21 | Night    | 89       | 1            |
| 10     | 2017-03-21 | Item     | 8        | 3            |
|--------|------------|----------|----------|--------------|

What I would like to get:

Month 01 (January) = 311.00$ (95+95+105+8+8)
Month 02 (February) = 295.00$ (95+95+95)
Month 03 (March) = 202.00$ (89+89+8+8+8)
Month 04 (April) = 0.00$
Month 05 (May) = 0.00$
...

Is it possible to make that with mySQL?

Do I nee to make several queries or I can make it in one ?

Thanks for any help.

Something like `SELECT BIL_Date,sum(BIL_Rate * BIL_Quantity) FROM tablename GROUP BY YEAR(BIL_Date),MONTH(BIL_Date);` — JustOnUnderMillions, Mar 15 '17 at 14:53

score 2 · Accepted Answer · answered Mar 15 '17 at 14:50

2

SELECT MONTH(BIL_Date) as Month, SUM(BIL_Rate*BIL_Quantity) as Total FROM ___BillableDatas GROUP BY YEAR(BIL_Date), MONTH(BIL_Date)

Did not tested it, but this should be the right query. It will return the Month and amount of that month.

answered Mar 15 '17 at 14:50

Scriptman

424
2
13

How I can generate the other month if I do not have any datas from them? Can I use an array? How to do this please? Thanks for your help. – F__M Mar 15 '17 at 15:37
One way is to use a predefined array. `$aStats = array ( ); $aStats[1] = 0; $aStats[2] = 0; etc` After that, in your while where you loop you database results: `$aStats[$aFetch['Month']] = $aFetch['Total'];` If you var_dump your `$aStats`, You'll have a full year with stats. But this is an other question. The answer can be found here: http://stackoverflow.com/a/31587871/6559606 – Scriptman Mar 16 '17 at 07:42

score 0 · Answer 2 · answered Mar 15 '17 at 14:50

0

Using a subquery to fiddle the data first makes the query easier to manipulate later (if required)

select a1.Period, 
       sum(a1.BIL_Rate_x) as Total_Amount
from 
  (
  select date_format(BIL_Date, '%Y %m') as Period, BIL_Rate * BIL_Quantity as BIL_Rate_x
  from `___BillableDatas`
  ) a1
group by a1.Period

answered Mar 15 '17 at 14:50

JohnHC

10,935
1
24
40

looks good too. Any idea on how I can generate a row for the other month for witch I do not have any datas yet ? – F__M Mar 15 '17 at 14:58
@qatari With MySQL, you'll need a numbers table, details [here](http://stackoverflow.com/questions/3538858/mysql-how-to-fill-missing-dates-in-range) – JohnHC Mar 15 '17 at 15:04

Rahul · Answer 3 · 2017-03-15T14:52:36.653

0

You need to use MONTH() function and group by like

select month(BIL_Date) as month,
sum(BIL_Rate * BIL_Quantity) as sumval
from `___BillableDatas`
group by year(BIL_Date), month(BIL_Date);

edited Mar 15 '17 at 14:52

answered Mar 15 '17 at 14:51

Rahul

76,197
13
71
125

Don't forget to group by year too ;-) – Guillaume Sainthillier Mar 15 '17 at 14:51
@Rahul. Wow thanks. How I can generate the other month if I do not have any datas from them ? – F__M Mar 15 '17 at 14:56

score 0 · Answer 4 · answered Mar 15 '17 at 14:54

0

I think you also should group on year, unless you have a table for each year

SELECT YEAR(BilDate) AS BillYear, MONTH(BilDate) AS BillMonth, SUM(BilRate * BilQuantity) AS Total
FROM __BillableDatas
GROUP BY YEAR(BilDate), MONTH(BilDate)

answered Mar 15 '17 at 14:54

Kevin

751
6
12

Stefano Zanini · Answer 5 · 2017-03-15T15:12:24.127

0

You can achieve something similar with this

select  month(BIL_DATE),
        sum(BIL_RATE * BIL_QUANTITY)
from    ___BillableDatas
group by year(BIL_DATE), month(BIL_DATE)

but it won't return the 0s for future months. To get those too, you should have a lookup table with all the months, and do a left join between that table and the result of this query, like

select  t1.month, coalesce(t2.bill, 0)
from    months t1
left join (
            select  year(BIL_DATE) year,
                    month(BIL_DATE) month,
                    sum(BIL_RATE * BIL_QUANTITY) bill
            from    ___BillableDatas
            group by year(BIL_DATE), month(BIL_DATE)
        ) t2
on      t1.month = t2.month and
        t1.year = t2.year

Edit

If you want to manipulate the data at application level, having an array of months already available, you can fetch the result of the first query in a map ['month' -> 'bill'] and then loop through your months array, like

for (month in months){
    if(map[month] == null){
        map.add(month, 0);
    }
}

Note that this is just pseudo code, as I'm not proficient in PHP.

edited Mar 15 '17 at 15:12

answered Mar 15 '17 at 14:55

Stefano Zanini

5,876
2
13
33

Can the others month without datas, come from another source like an array in PHP ? – F__M Mar 15 '17 at 15:00
Yes, if you can manipulate the resultset at application level, there's no need to do the left join. You can get the values for the months you have, and then combine them with the months list with PHP. – Stefano Zanini Mar 15 '17 at 15:01
could please update your second exemple with your idea please? – F__M Mar 15 '17 at 15:05
I edited my answer with some pseudo code, just to give you the idea – Stefano Zanini Mar 15 '17 at 15:13

Sum datas by date with MySql

5 Answers5