mysql query with subquery loop?

Question

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.

PinnyM · Accepted Answer · 2012-09-21 13:55:56Z

2

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

edited Sep 21, 2012 at 13:55

answered Sep 21, 2012 at 13:49

PinnyM

35.6k4 gold badges77 silver badges84 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

adam Kearsley Over a year ago

Tested in PhpMyAdmin and got --- #1221 - Incorrect usage of CUBE/ROLLUP and ORDER BY

PinnyM Over a year ago

You are correct, WITH ROLLUP precludes the use of ORDER BY and does the sorting for you already - so you can skip the ORDER BY clause. Updated.

Lukos · Accepted Answer · 2012-09-21 13:49:45Z

0

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

edited Sep 21, 2012 at 13:49

answered Sep 21, 2012 at 13:49

Lukos

1,8622 gold badges15 silver badges29 bronze badges

Comments

Anshu · Accepted Answer · 2012-09-21 13:55:36Z

0

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!

answered Sep 21, 2012 at 13:55

Anshu

7,8635 gold badges33 silver badges41 bronze badges

1 Comment

rivimey Over a year ago

Would be better to express the join explicitly by moving the t.brid=m.brid into the from clause.

Collectives™ on Stack Overflow

mysql query with subquery loop?

3 Answers 3

2 Comments

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related