Somewhat complicated query

4 Replies - 2206 Views - Last Post: 12 April 2006 - 06:30 PM

Need a count from only one table

Posted 11 April 2006 - 08:03 PM

I need some assistance with a query in a MySQL database.

I have a query that reports the total quantity of products sold on each date greater than a given date.

The order dates are stored in a table that has one record per order. The line items are stored in a second table, with "orderid" as the key between them. A single line item can specify a quantity greater than 1. A single order can have several line items. The dates are stored as UNIX timestamps in a field called "date". (Yes, I know that's bad form naming a field 'date', but I'm stuck with the database that came with the shopping cart.)

Right now the query looks like this:

SELECT date_format(FROM_UNIXTIME(a.date), '%m-%d-%Y') as orderdate,
date_format(FROM_UNIXTIME(a.date), '%W') as dayofweek,
sum(b.quantity) as totalsold
FROM orders a, order_details b
WHERE a.orderid=b.orderid and FROM_UNIXTIME(a.date) > "2006-04-05"
GROUP BY orderdate;

What I want to do is add a column to the query that shows the total number of orders that day. I can't use count(*) because that returns the total number of line items (from order_details) instead of the total number of orders (from orders).

For instance, on 04-07-2006, we have 47 orders with a total of 54 line items, and a total of 62 units sold. (Thanks to quantities greater than 1 on some line items.)