-1

I have two simple table setup:

table: deals
dealID   |   dealDate    | storeName
1        |   2013-04-01  | bayport
2        |   2013-04-01  | staten island
3        |   2013-04-04  | bayport



table: phoneDetails
phoneID    |   dealID    | dateReceived
1          |   1         | 2013-04-04
2          |   1         | 2013-04-04
3          |   2         | 2013-04-06
4          |   3         | 2013-04-05
5          |   3         | 2013-04-05

So based on this table, I need to query my db to show the dealDate and how many phones were received on that date per store OR even using calendar dates instead of dealDate will work too. For example:

dealDate    |  storeName     | Received
2013-04-01  |  bayport       | 0
2013-04-01  |  staten island | 0
2013-04-04  |  bayport       | 2

Any help would be appreciated. I know how to query the dealDate and storeName but I am confused on how to subquery the 'Received' count since the dealDate and the dateReceived are different.

@echo_me, what do I do to change the result to look like this instead which is based on the calendar instead of the dealDate. See how I received 1 phone on April 6 even though there was no deal done on that date

date       |  storeName      |  received
2013-04-01 |                 |  0
2013-04-02 |         |                          
2013-04-03 |                 |
2013-04-04 | bayport         |  2
2013-04-05 | bayport         |  1
2013-04-06 | staten island   |  1
Renee Cribe
  • 325
  • 1
  • 4
  • 14
  • You only want to count phones received on the same date as `dealDate`? – Barmar Apr 10 '13 at 18:36
  • I can't figure out your example. Is the `dealDate` column supposed to come from `deals.dealDate` or `phoneDetails.dateReceived`? The count in the `Received` column suggests the latter. But then why are there rows for `2013-04-01` when that date doesn't appear in the table? – Barmar Apr 10 '13 at 18:48
  • I'm unclear what the received column is in your example result. Why is it zero for the first two? Aren't those both received? – Tim Gautier Apr 10 '13 at 19:22
  • @Barmar yes , i only need to count phones received on the dealDate. The situation is that the deal could have been done today 2013-04-11 but we still need to order the phones. so we receive those phones not on the same day as the dealDate. But we still receive phone ordered from previous dates so basically i am simply counting how many phones I received today. – Renee Cribe Apr 10 '13 at 21:39
  • Please explain your example. Why does it say 2 phones received in bayport on 2013-04-04? The only `dealID` with that `dealDate` is 3, it was received on 2013-04-05, and there's only one phone with that `dealID`. – Barmar Apr 10 '13 at 22:54
  • @Barmar it shows two because on dateReceived there was two phones received for dealID 1 which is from Bayport so basically if i were to count everything that I need on 2013-04-04 there was 1 deal made and 2 phones received – Renee Cribe Apr 10 '13 at 23:09
  • You said "I only need to count phones received on the dealDate". dealID 1 has dealDate 2013-04-01, the phones were received on 2013-04-04, so they were NOT received on the dealDate, so they shouldn't be counted. – Barmar Apr 10 '13 at 23:16
  • Yes, but there where other phones received on that dealDate, I was basically using the dealDate as reference point. if it would make it easier, I edited the question so that instead of the dealDate the result will be displayed based on the calendar date. if you please check my edited question – Renee Cribe Apr 10 '13 at 23:23

3 Answers3

1

I think this will produce the results in your example:

select d.dealDate dealDate, storeName, ifnull(count(*), 0) Received
from deals d
left join phoneDetails p on d.dealId = p.dealId and d.dealDate = p.dateReceived
group by dealDate, storeName

For the second result you asked for, this should do it:

select dateReceived date, storeName, count(*) received
from phoneDetails p
join deals using (dealId)
group by date, storeName
order by date

This won't show days when nothing was received, you need to join with a table that contains all the dates in the range you care about, and there's no built-in way to do that in MySQL (I think SQL-Server or Oracle can do it). See this question for a way to fill in a temporary table with all the dates, then you can left join that with the above query to produce the 0 rows that you want.

Community
  • 1
  • 1
Barmar
  • 741,623
  • 53
  • 500
  • 612
  • wow thanks..i totally skipped the ON dealDate = dateReceived, by mind was over-complicating it for me – Renee Cribe Apr 10 '13 at 21:43
  • I tried my query on your sample data, it doesn't produce the same results as your example. So either your example is wrong or I misunderstand what you want. – Barmar Apr 10 '13 at 22:55
  • Hi Barmar thanks for the replies, I marked your answer as useful because I was was just looking for the d.dealDate = p.dateReceived condition which was what I was missing. – Renee Cribe Apr 10 '13 at 23:12
  • I added a new query that shows phones received by `dateReceived`. I'm not sure what you mean by calendar date, that doesn't exist in your tables. Either we display results by `dealDate` or `dateReceived`. – Barmar Apr 11 '13 at 01:56
  • Thanks Barmar. I'll just go with the temporary table with the calendar date then join it with the other tables. Thanks for the link and the query =) – Renee Cribe Apr 11 '13 at 02:36
1

try this

SELECT a.dealDate as dealDate, a.storeName as storeName, count(dateReceived) as Received
FROM deals a 
LEFT JOIN phoneDetails b 
ON a.dealDate = b.dateReceived
GROUP BY dealDate, storeName

DEMO HERE

echo_Me
  • 37,078
  • 5
  • 58
  • 78
  • wow thanks..i totally skipped the ON dealDate = dateReceived, by mind was over-complicating it for me – Renee Cribe Apr 10 '13 at 21:42
  • another quick question, bec obviously with this setup that the result is based on the dealDate if there was a no deal done on that date it will not give me a count of the received. What do I change so that the result will show month days instead – Renee Cribe Apr 10 '13 at 22:51
  • what you mean month days ? the name of month ? – echo_Me Apr 10 '13 at 22:54
  • its tottally diferent from the first results. – echo_Me Apr 10 '13 at 23:18
  • yes, the edited result i posted would be based on the calendar instead of the dealDate so that even if there was no deal done on that date as long as a phone was received it will still display it on the result – Renee Cribe Apr 10 '13 at 23:24
0

If you try something like the following it would return the counts where the received date is later than the deal date.

select a.dealDate as dealDate, a.storeName as storeName, b.dateReceived as dateReceived,  count(*) as counts
  from deals a left outer join phoneDetails b on a.dealId = b.dealId
 where b.dateReceived >= a.dealDate
 group by dealDate, storeName, dateReceived

Does this get you to what you need?

Jerry Hoerig
  • 146
  • 9