* Chuck Barnett
> Hi I'm looking to create a query that will look through my previous orders
> and create a table of what items have sold with what products.
>
> My current table that holds the order details is set up like this.
> orderID, productID, quantity.
>
> Any suggestions would be appreceiated.
Maybe something like this:
mysql> use test
mysql> create table orders (orderID int,productID int,quantity int);
Query OK, 0 rows affected (0.42 sec)
mysql> insert into orders values
(1,1,1),(1,2,1),(2,1,2),(2,3,1),(3,1,1),
(3,2,2),(4,1,1),(4,2,1),(5,2,1),(6,3,1);
Query OK, 10 rows affected (0.03 sec)
Records: 10 Duplicates: 0 Warnings: 0
select o2.productID, sum(o2.quantity) qty, count(o2.quantity) cnt
from orders o,orders o2
where
o2.orderID = o.orderId and
o2.productID != o.productID and
o.productID = 1
group by o2.productID
order by qty desc;
+-----------+------+-----+
| productID | qty | cnt |
+-----------+------+-----+
| 2 | 4 | 3 |
| 3 | 1 | 1 |
+-----------+------+-----+
2 rows in set (0.00 sec)
Use 'having' to select only those with qty or cnt more than x.
--
Roger
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
