[wzeller]

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;

Which, today, gives a result of:

+------------+-----------+-----------+
| orderdate  | dayofweek | totalsold |
+------------+-----------+-----------+
| 04-05-2006 | Wednesday |        76 |
| 04-06-2006 | Thursday  |        60 |
| 04-07-2006 | Friday    |        62 |
| 04-08-2006 | Saturday  |        50 |
| 04-09-2006 | Sunday    |        49 |
| 04-10-2006 | Monday    |        81 |
| 04-11-2006 | Tuesday   |        53 |
+------------+-----------+-----------+
7 rows in set (0.54 sec)

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.)

I can get the total number of orders for a given day with this:

SELECT count(*) 
FROM orders 
WHERE date_format(FROM_UNIXTIME(date), '%m-%d-%Y') = "04-07-2006";

which returns 47. But how can I add that to the original query to show the total number of orders per day in addition to the total units sold per day?

Sorry to be so lengthy, but I wanted to get all the info out there that somebody would need for helping me out.

Thanks,
Wayne

This post has been edited by wzeller: 11 April 2006 - 08:06 PM

[Amadeus]

Have you tried adding a COUNT(a.orders) to the query? Sorry for the shot in the dark, without knowing the full layout of the tables, it's kind of difficult to suggest.

[Jayman]

I'm not sure how similar SQL and MySQL are, but in SQL you would nest the second select statement inside the first one. This will create a 4th column of data.

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, (SELECT count(*)
FROM orders) as totalorders 
FROM orders a, order_details b
WHERE a.orderid=b.orderid and FROM_UNIXTIME(a.date) > "2006-04-05"
GROUP BY orderdate;

They should be fairly similar languages, so you may have to adjust the syntax for it to work in MySQL.

This post has been edited by jayman9: 11 April 2006 - 10:04 PM

[wzeller]

Both of these are excellent suggestions.

I had actually tried the count(orders.*) idea, and got a syntax error. I should have mentioned that, but the post was already so lengthy.

The subquery would be the ideal solution, except that the hosting provider has me on an old version of MySQL that doesn't support subqueries.

Surely, there must be some way to ask for the number of parent records in the join. I just can't figure it out yet.

Any other suggestions?

Wayne

[wzeller]

Actually, just now I stumbled upon the solution - and it uses a syntax I didn't know you could use.

The field is actually "count(distinct(a.orderid))". There's one you never see in a manual!

In case somebody else ever needs something similar, here's the entire working query:

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, 
count(distinct(a.orderid)) as numorders 
FROM orders a, order_details b 
WHERE 
a.orderid=b.orderid and 
FROM_UNIXTIME(a.date) > "2006-01-01" 
GROUP BY 
orderdate;

Wayne