CODEWITHSUNDEEP
mysqlloops
adam Kearsley
adam Kearsley

Reputation: 999

mysql query with subquery loop?

I have a query

SELECT 
  count(product) as amount,
  product, 
  sum(price) AS price 
FROM `products` 
WHERE 
  brid = 'broker' 
AND 
  cancelled is null 
GROUP BY product 
WITH ROLLUP

Is it possible to query a table to get a brokers id and then for each broker run the query above written as 1 query?

Almost like:

SELECT brid FROM membership
THEN

SELECT 
  count(product) as amount,
  product, 
  sum(price) AS price 
FROM `products` 
WHERE 
  brid = membership.brid 
AND 
  cancelled is null 
GROUP BY product 
WITH ROLLUP

THEN
SELECT NEXT brid

Is this possible? i know how to do it in PHP but i would prefer 1 query that can create an array rather than tons of queries for each.

Thanks Adam.

Upvotes: 1

Views: 4535

Answers (3)

PinnyM
PinnyM

Reputation: 35531

Sure, you can GROUP BY both the 'brid' field and the 'product' field. As noted below, WITH ROLLUP will cause it to sort by 'brid' and then by 'product':

SELECT
  brid, 
  count(product) as amount,
  product, 
  sum(price) AS price 
FROM `products` 
WHERE 
  brid IN (SELECT brid FROM membership)
AND 
  cancelled is null 
GROUP BY brid, product 
WITH ROLLUP

Upvotes: 2

Anshu
Anshu

Reputation: 7853

As far as I can understand from your example, all you need is inner join between membership and products on brid

Take the following example:

products table:

CREATE TABLE `test` (
  `price` int(11) DEFAULT NULL,
  `product` varchar(20) DEFAULT NULL,
  `brid` varchar(20) DEFAULT NULL,
  `cancelled` varchar(20) DEFAULT NULL
)

membership table:

CREATE TABLE `membership` (
  `brid` varchar(20) DEFAULT NULL
)

And following is my query as you required:

SELECT 
  t.brid, count(t.product) as amount,
  t.product, 
  sum(t.price) AS price 
FROM products t, membership m
WHERE 
  t.brid = m.`brid`
AND 
  cancelled is null 
GROUP BY product

Hope that helps!

Upvotes: 0

Lukos
Lukos

Reputation: 1852

SELECT 
  count(product) as amount,
  product, 
  sum(price) AS price 
FROM `products` 
INNER JOIN membership on membership.brid = products.brid
WHERE 
  cancelled is null 
GROUP BY product 
WITH ROLLUP

Upvotes: 0

Related Questions