mysql query result is not displaying some rows

Question

I am confused on how to query the following table. I simply want to show a count of how many was ordered and received on a specific date.

calendar table

id  |  date
1   |  2013-07-01 
2   |  2013-07-02 
3   |  2013-07-03 
4   |  2013-07-04 
5   |  2013-07-05
6   |  2013-07-06
7   |  2013-07-07

phone_details table

id   |  dateOrdered    | dateReceived | upg
1    |  2013-07-01     | 2013-07-05   | exp
2    |  2013-07-02     | 2013-07-05   | post
3    |  2013-07-02     | 2013-07-06   | upgrade
4    |  2013-07-07     | 2013-07-07   | upggrade
5    |  2013-07-03     | 2013-07-04   | exp
6    |  2013-07-01     | 2013-07-02   | exp

What I would like the result to be.

calendar.date  | noOrdered   |  noReceived  | # of post  | # of exp  | # of upgrade
2013-07-01     | 2           |              |            | 2         |
2013-07-02     | 2           |  1           | 1          |           | 1
2013-07-03     | 1           |              |            | 1         |
2013-07-04     |             |  1           |            |           |
2013-07-05     |             |  2           |            |           |
2013-07-06     |             |  1           |            |           |
2013-07-07     | 1           |  1           |            | 1         |

Here is my query:

select calendar.date,DAYNAME(calendar.date) as `day`,
sum(if((`phone_details`.`upg` = 'Post'),1,0)) AS `Post Paid`,
sum(if((`phone_details`.`upg` = 'Upgrade'),1,0)) AS `Upgrade`,
sum(if(((`phone_details`.`upg` = 'Exp') or (`phone_details`.`upg` = 'Future Exp')),1,0)) AS `Exp`,
(select count(phone_ID) FROM phone_details 
        WHERE dateReceived = calendar.date 
        )AS `received`

from `phone_details` JOIN calendar
where calendar.date = phone_details.dateOrdered

group by calendar.date DESC

The problem with this query is if there is nothing ordered on a date, it does not display it on the result so even if there was a receive on that date it still is not displayed. My result simply looks like the table below instead of the one above. If I subquery each column, I am able to produce the result I want but the processing time seems to slow down considerably.

calendar.date  | noOrdered   |  noReceived  | # of post  | # of exp  | # of upgrade
2013-07-01     | 2           |              |            | 2         |
2013-07-02     | 2           |  1           | 1          |           | 1
2013-07-03     | 1           |              |            | 1         |
2013-07-07     | 1           |  1           |            | 1         |

Some guidance would be appreciated. Thanks very much.

Joachim Isaksson · Accepted Answer · 2013-07-06 07:34:13Z

4

You'll want a LEFT JOIN to generate results for each row in calendar even if they don't match anything in your phone_details table;

SELECT c.date "calendar_date", 
  SUM(c.date=pd.dateOrdered) noOrdered,
  SUM(c.date=pd.dateReceived) noReceived,
  SUM(c.date=pd.dateOrdered AND upg='post')    "# of post",
  SUM(c.date=pd.dateOrdered AND upg='exp')     "# of exp",
  SUM(c.date=pd.dateOrdered AND upg='upgrade') "# of upgrade"
FROM calendar c
LEFT JOIN phone_details pd
  ON c.date = pd.dateOrdered
  OR c.date = pd.dateReceived
GROUP BY c.date;

An SQLfiddle to test with.

answered Jul 6, 2013 at 7:34

Joachim Isaksson

182k28 gold badges297 silver badges307 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

user359040 Over a year ago

+1 Both for the join, and for adding the additional columns that the OP wanted but did not include in the query.

Renee Cribe Over a year ago

thanks very much this works great, I tried left join before i posted here and I think i got the date condition reversed thats why it didn't work the first time. thanks again

bhttoan · Accepted Answer · 2013-07-06 07:21:07Z

0

I don't think your join is correct as you should use an on and not a where :

from `phone_details` JOIN calendar
where calendar.date = phone_details.dateOrdered

Try using:

from `phone_details` JOIN calendar
on calendar.date = phone_details.dateOrdered

answered Jul 6, 2013 at 7:21

bhttoan

2,7566 gold badges48 silver badges76 bronze badges

Comments

harsh · Accepted Answer · 2013-07-06 07:42:40Z

-1

select calendar.date,DAYNAME(calendar.date) as `day`,
sum(if((`phone_details`.`upg` = 'Post'),1,0)) AS `Post Paid`,
sum(if((`phone_details`.`upg` = 'Upgrade'),1,0)) AS `Upgrade`,
sum(if(((`phone_details`.`upg` = 'Exp') or (`phone_details`.`upg` = 'Future Exp')),1,0)) AS `Exp`,
(select count(id) FROM phone_details 
        WHERE dateReceived = calendar.date 
        )AS `received`

from `phone_details` JOIN calendar
ON calendar.date = phone_details.dateOrdered or calendar.date = phone_details.dateReceived

group by calendar.date DESC

This works fine. You can check here http://sqlfiddle.com/#!2/2b9ca/3

answered Jul 6, 2013 at 7:42

harsh

1,55113 silver badges15 bronze badges

Collectives™ on Stack Overflow

mysql query result is not displaying some rows

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related