Query help - Join on date, group by date

[ajcolburn]

Hi there,

I have 2 tables, shopOrder and wholesaleOrder, each have the following structure:

orderID, orderDate, orderTotal

I want to generate a report with the following structure, between two given dates:

Date, Number of Wholesale Orders, Wholesale Orders Total, Number of Shop Orders, Shop Orders Total

Currently, my query only returns order data from the shopOrder table if orders exist in the wholesaleOrder table for that date, otherwise just wholesaleOrder data is returned. Ideally, i'd like to end up with with a query that can output data as shown in the table below (ie. shop order data is shown for a date even if there are no wholesale orders for that date and vice versa):

[HTML]<table>
<thead>
<tr>
<th>Date</th>
<th>Number of Wholesale Orders</th>
<th>Wholesale Orders Total</th>
<th>Number of Shop Orders</th>
<th>Shop Orders Total</th>
</tr>
</thead>
<tbody>
<tr>
<td>11/05/2008</td>
<td>1</td>
<td>100.00</td>
<td>2</td>
<td>50.00</td>
</tr>
<tr>
<td>13/05/2008</td>
<td>0</td>
<td>0</td>
<td>4</td>
<td>130.00</td>
</tr>
<tr>
<td>14/05/2008</td>
<td>3</td>
<td>37.00</td>
<td>0</td>
<td>0</td>
</tr>
</tbody>
</table>[/HTML]

Any help with the SQL query would be greatly appreciated! Here is my current query:

SELECT wholesaleOrder.orderDate,
SUM(wholesaleOrder.orderTotal) AS wholesaleOrdersTotal,
COUNT(wholesaleOrder.orderID) AS wholesaleOrdersQuantity
SUM(shopOrder.orderTotal) AS shopOrdersTotal,
COUNT(shopOrder.orderID) AS shopOrdersQuantity 
FROM wholesaleOrder
LEFT JOIN shopOrder ON shopOrder.orderDate=wholesaleOrder.orderDate 
WHERE wholesaleOrder.orderDate BETWEEN $date1 AND $date2 
OR shopOrder.orderDate BETWEEN $date1 AND $date2
GROUP BY wholesaleOrder.orderDate

Kind Regards,
Alex

[code green]

shop order data is shown for a date even if there are no wholesale orders for that date and vice versa

I don't quite understand. This sounds like you just want all the data from both tables.

orderID, orderDate, orderTotal

There is no database structure here.
It is simply two flat files with shopOrder and wholesaleOrder data.
A common field is needed between the two tables for a JOIN.
As suggested orderDate is not a common field.
It may be a UNION you are looking for

[ajcolburn]

Hi there,

Thanks for your reply,

I haven't explained this very well, I aim to generate a report which combines totals from the shopOrder and wholesaleOrder tables by date, for a given date span. I can achieve this with nested queries and php loops, although I would be interested to know whether this could be done with a single query.

Here is a screenshot of the intended output:

Report Screenshot

Kind Regards,
Alex

[Atli]

Hi.

Have you considered executing this as two statements, joined by a UNION.
Like:

SELECT SUM(total), count(id) FROM table1
WHERE dateField BETWEEN start AND end
UNION
SELECT SUM(total), count(id) FROM table2
WHERE dateField BETWEEN start AND end
 

That would give you two rows, one for each table.