MySQL SQL: SELECT (SELECT... as variable) FROM

Question

I have a simple question but I can't find an answer (please give me a link if that question is on stackoverflow forum). Table has orderid and quantity columns. How to select orderid, quantity, total_in_order, where total_in_order is a sum of quantity for current orderid So the table looks the same, but with additional column. Example:

orderid - quantity - **total_in_order**
 1      -  43      - **78**
 1      -  24      - **78**
 1      -  11      - **78**
 2      -  31      - **31**
 3      -   9      - **25**
 3      -  16      - **25**

So, how to modify SELECT orderid, quantity, SUM(quantity) from orders; ? Thank you.

Mark Byers · Accepted Answer · 2011-11-24 20:09:22Z

3

Use a join to a subselect. In the subselect calculate the totals for each orderid.

SELECT
    orders.orderid,
    orders.quantity,
    totals.total_in_order
FROM orders
JOIN
(
    SELECT orderid, SUM(quantity) AS total_in_order
    FROM orders
    GROUP BY orderid
) AS totals
ON orders.orderid = totals.orderid

answered Nov 24, 2011 at 20:09

Mark Byers

844k202 gold badges1.6k silver badges1.5k bronze badges

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

1 Comment

Edouard Tabet Over a year ago

Looks like I gave the exact same answer you did. Sorry I didn't see yours before I posted mine.

dani herrera · Accepted Answer · 2011-11-24 20:09:16Z

1

First approach:

SELECT 
   O.orderid, 
   O.quantity, 
   ( select sum( O2.quantity) 
     from orders O2
     where O.orderid = O2.orderid ) as qty
from 
   orders O

answered Nov 24, 2011 at 20:09

dani herrera

52k11 gold badges133 silver badges188 bronze badges

1 Comment

Andreas Wederbrand Over a year ago

I believe this would make a sub select for each row in orders, you should check out @Marks answer.

Edouard Tabet · Accepted Answer · 2011-11-24 20:23:15Z

0

Here is an example on how to do it using a subquery. It should do the job.

SELECT A.orderid
   A.quantity
   B.sum
FROM 
   orders A JOIN ( SELECT orderid, sum(quantity) as sum from orders group by orderid) as B on(A.orderid=B.orderid)

answered Nov 24, 2011 at 20:23

Edouard Tabet

1581 silver badge10 bronze badges

Collectives™ on Stack Overflow

MySQL SQL: SELECT (SELECT... as variable) FROM

3 Answers 3

1 Comment

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related