MySQL Query, how to group and count in one row ?
Posted
by
Akarun
on Stack Overflow
See other posts from Stack Overflow
or by Akarun
Published on 2011-03-20T15:59:06Z
Indexed on
2011/03/20
16:10 UTC
Read the original article
Hit count: 211
Hi All,
To simplify, I have tree tables: products
, products-vs-orders
, orders
products
fields : 'ProductID', 'Name', 'isGratis', ...products-vs-orders
fields : 'ProductID', 'OrderID'orders
fields : 'OrderID', 'Title', ...
Actually, I have a query like this:
SELECT orders.OrderID, orders.Title, COUNT(`products`.`isGratis`) AS "Quantity", `products`.`isGratis`
FROM `orders`, `products-vs-orders`, `products`
WHERE `orders`.`OrderID` = `products-vs-orders`.`OrderID` AND `products-vs-orders`.`ProductID` = `products`.`ProductID`
GROUP BY `products`.`PackID`, `products`.`isGratis`
This query works and return this surch of result:
OrderID, Title, Quantity, isGratis
1 My Order 20 0
1 My Order 3 1
2 An other 8 0
2 An other 1 1
How can I retrieve the count of products 'gratis' and 'paid' in to separate cols ?
OrderID, Title, Qt Paid, Qt Gratis
1 My Order 20 3
2 An other 8 1
Thanks for your help
© Stack Overflow or respective owner