Counting Records with Related tables

[Shanondink]

Hi guys,

I have 2 tables that I keep orders in.

One has order info (SS_orders has dates, shipping info, etc).

[tt]
mysql> SELECT
-> SS_orders.index,
-> SS_orders.date_received
-> FROM SS_orders
-> WHERE
-> SS_orders.date_received < '2008-11-01'
-> ORDER BY `index` DESC
-> LIMIT 10;
+-------+---------------------+
| index | date_received |
+-------+---------------------+
| 45171 | 2008-10-19 21:41:22 |
| 45170 | 2008-10-19 20:40:24 |
| 45169 | 2008-10-19 20:23:46 |
| 45168 | 2008-10-19 20:10:16 |
| 45167 | 2008-10-19 18:07:34 |
| 45166 | 2008-10-19 17:36:31 |
| 45165 | 2008-10-19 17:36:07 |
| 45164 | 2008-10-19 17:34:30 |
| 45163 | 2008-10-19 17:20:40 |
| 45162 | 2008-10-19 17:17:49 |
+-------+---------------------+
10 rows in set (0.00 sec)
[/tt]

And the other has item info (item name, item price, etc).
[tt]
mysql> SELECT
-> SS_orders_items.index,
-> SS_orders_items.orders_index,
-> SS_orders_items.item_name
-> FROM SS_orders_items
-> ORDER BY `index` DESC
-> LIMIT 10;
+-------+--------------+-----------+
| index | orders_index | item_name |
+-------+--------------+-----------+
| 20391 | 45171 | item_1 |
| 20390 | 45171 | item_1 |
| 20389 | 45170 | item_1 |
| 20388 | 45169 | item_3 |
| 20387 | 45168 | item_1 |
| 20386 | 45167 | item_1 |
| 20385 | 45166 | item_1 |
| 20384 | 45165 | item_1 |
| 20383 | 45164 | item_2 |
| 20382 | 45164 | item_2 |
+-------+--------------+-----------+
10 rows in set (0.00 sec)
[/tt]

SS_orders_items.orders_index is related to SS_orders.index.

The following SQL, returns the records I'm looking for, but I can't figure out how to make it count how many orders we've processed in October

[tt]
mysql> SELECT
-> MONTH( SS_orders.date_received ) AS 'MONTH',
-> SS_orders_items.item_name AS 'ITEM_NAME'
-> FROM `SS_orders`
-> LEFT JOIN `SS_orders_items` ON SS_orders_items.orders_index = SS_orders.index
-> WHERE
-> SS_orders.customer_number = 10069 AND
-> SS_orders.status not Like 'cancelled%' AND
-> SS_orders.date_received > '2008-10'
-> ORDER BY `SS_orders`.`index` DESC
-> LIMIT 10;
+-------+-----------+
| MONTH | ITEM_NAME |
+-------+-----------+
| 10 | item_1 |
| 10 | item_1 |
| 10 | item_1 |
| 10 | item_3 |
| 10 | item_1 |
| 10 | item_1 |
| 10 | item_1 |
| 10 | item_1 |
| 10 | item_2 |
| 10 | item_2 |
+-------+-----------+
10 rows in set, 1 warning (0.00 sec)
[/tt]

So for the above set, I need to get back something like this:

[tt]
+-------+-----------+---------+
| MONTH | ITEM_NAME | # Items |
+-------+-----------+---------+
| 10 | item_1 | 7 |
| 10 | item_2 | 2 |
| 10 | item_3 | 1 |
+-------+-----------+---------+
[/tt]

I have been trying for 2 days now to figure out a query that would summarize the number of items I sold last month.

Can help or somebody point me to where I can figure out how count records in this way?

Thanks in advance,
Shanon

 

[IanWaterman]

mysql> SELECT count(*) as Total_orders,
-> MONTH( SS_orders.date_received ) AS 'MONTH',
-> SS_orders_items.item_name AS 'ITEM_NAME'
-> FROM `SS_orders`
-> LEFT JOIN `SS_orders_items` ON SS_orders_items.orders_index = SS_orders.index
-> WHERE
-> SS_orders.customer_number = 10069 AND
-> SS_orders.status not Like 'cancelled%' AND
-> SS_orders.date_received > '2008-10'
-> Group BY `SS_orders`.`index` DESC

Ian

 

[Shanondink]

Thanks Ian,

Yes that spits out records, but it doesn't get what I want.

I have the following working for this month. But if I change the WHERE to include last month and this month, it sums them all on one line instead of splitting them separate lines for each month/item_name.

SELECT DATE, ITEM_NAME, SUM(COUNT) AS NUM_DEVICES FROM
 (
 SELECT
 (SELECT MONTH(SS_orders.date_received) FROM SS_orders WHERE SS_orders.index = SS_orders_items.orders_index) AS DATE
 ,SS_orders_items.item_name AS ITEM_NAME
 ,count(SS_orders_items.item_name) AS COUNT
FROM SS_orders_items
LEFT JOIN SS_orders ON SS_orders_items.orders_index = SS_orders.index
WHERE 1
 AND SS_orders.customer_number = 10069
 AND SS_orders.status not Like 'cancelled%'
 AND SS_orders.date_received > '2008-11-01'
#GROUP BY SS_orders_items.orders_index, SS_orders_items.item_name
GROUP BY item_name, DATE
ORDER BY SS_orders_items.orders_index DESC
#LIMIT 10000
 ) AS k
GROUP BY k.item_name

I would like it to spit out something like the following:

[tt]
+-------+-----------+---------+
| MONTH | ITEM_NAME | # Items |
+-------+-----------+---------+
| 11 | item_1 | 15 |
| 11 | item_2 | 6 |
| 11 | item_3 | 4 |
| 10 | item_1 | 7 |
| 10 | item_2 | 2 |
| 10 | item_3 | 1 |
+-------+-----------+---------+
[/tt]

Any idea where to look?

Normally I would grab the data with PHP and work it there, but I'm trying to utilize the power of MySQL since it could be used for summing data from 30,000 records.

Thanks again,
Shanon

 

[IanWaterman]

Its because your final select is not grouping my month

SELECT DATE, ITEM_NAME, SUM(COUNT) AS NUM_DEVICES FROM
(
SELECT
(SELECT MONTH(SS_orders.date_received) FROM SS_orders WHERE SS_orders.index = SS_orders_items.orders_index) AS DATE
,SS_orders_items.item_name AS ITEM_NAME
,count(SS_orders_items.item_name) AS COUNT
FROM SS_orders_items
LEFT JOIN SS_orders ON SS_orders_items.orders_index = SS_orders.index
WHERE 1
AND SS_orders.customer_number = 10069
AND SS_orders.status not Like 'cancelled%'
AND SS_orders.date_received > '2008-11-01'
#GROUP BY SS_orders_items.orders_index, SS_orders_items.item_name
GROUP BY item_name, DATE
ORDER BY SS_orders_items.orders_index DESC
#LIMIT 10000
) AS k
GROUP BY k.DATE, k.item_name

Not sure why you are doing the select on a select.
You select K should give you what you want if you extend the date.

Ian

 

[Shanondink]

Thanks Ian!

Exactly what I want.

I'll see if I can trim off that extra select

Thanks again,
Shanon