CODEWITHSUNDEEP
mysqlgroup-by
user2070749
user2070749

Reputation: 59

mysql group return all row

i have a question here..

lets say i have this table...

waktu               |   tglTransaksiJual   | nominal   | ongkir    | STATUS    | idUser
---------------------------------------------------------------------------------------
2013-02-01 00:00:00 | 2013-01-29 15:00:00  | 10000     | 10000     | APPROVED  | 1
0000-00-00 00:00:00 | 2013-02-02 15:00:00  | 20000     | 0         | DONE      | 2
0000-00-00 00:00:00 | 2013-02-02 15:00:00  | 10000     | 5000      | DONE      | 2
2013-02-03 00:00:00 | 2013-02-01 15:00:00  | 20000     | 0         | APPROVED  | 3

AND i use this query

SELECT DISTINCT(date(IF(waktu = '0000-00-00 00:00:00', tglTransaksiJual, waktu))) AS thedate, SUM( nominal+ongkir ) AS total
        FROM transaksijual
        WHERE IF(waktu = '0000-00-00 00:00:00', tglTransaksiJual, waktu) BETWEEN '2013-02-01' AND '2013-02-05' AND status LIKE 'DONE%' AND idUser LIKE '2%'    
        GROUP BY thedate
        ORDER BY thedate ASC

But the result i have is

thedate    | total
-------------------
2013-02-01 | 30000

And that i want is like this

thedate    | total
------------------
2013-02-01 | 0
2013-02-02 | 35000
2013-02-03 | 0

How can i do that?? is that possible?? so the conclusion is.. when there's no data i want to get 0 values and get the row...

Upvotes: 1

Views: 502

Answers (1)

Explosion Pills
Explosion Pills

Reputation: 191749

Remove the WHERE condition and update the SUM column to only sum if the condition is met:

SUM(IF(status = 'DONE' AND idUser = '2', nominal + ongkir, 0))

Upvotes: 1

Related Questions