SQL query within another query

Question

I have a table containing customers and another containing all orders.

I want to display a list of customers and along side show the total value of their orders.

Obviously I could loop through the customers and then using PHP run another query to get each customer's revenue. I don't think this is efficient.

I am looking to achieve something like this:

SELECT username, [SELCT sum(revenue) from orders where userID=userID] from customers

And for this to show output:

bob      10000
jeff     25000
alan     500

you can always compare the performance between join and correlated subqueries. — John Woo
– John Woo, Commented Jan 25, 2013 at 16:06

John Woo · Accepted Answer · 2013-01-25 15:57:15Z

1

SELECT  a.username, SUM(b.revenue) totalRevenue
FROM    customers a
        LEFT JOIN Orders b
            ON a.userID = b.UserID
GROUP   BY a.username

This will list all customers with or without Orders.

To further learn more about join, please visit the article below,

Visual Representation of SQL Joins

answered Jan 25, 2013 at 15:57

John Woo

265k70 gold badges509 silver badges500 bronze badges

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

Comments

Derek · Accepted Answer · 2013-01-25 15:57:23Z

0

you're close...

 SELECT username, (SELECT sum(revenue) from orders where userID=c.userID) rev 
 from customers c

answered Jan 25, 2013 at 15:57

Derek

23.5k8 gold badges55 silver badges61 bronze badges

Comments

juergen d · Accepted Answer · 2013-01-25 15:57:42Z

0

You can join the tables and the group them by the order name

SELECT o.username, 
       sum(revenue) as sum_revenue
from orders  o
left outer join customers c on c.userid = o.userid
group by o.username

answered Jan 25, 2013 at 15:57

juergen d

205k40 gold badges305 silver badges377 bronze badges

Comments

Kickstart · Accepted Answer · 2013-01-25 15:58:24Z

0

No need for a subselect with that. Try something like this:-

SELECT customers.userID, customers.username, SUM(revenue)
FROM customers INNER JOIN orders ON customers.userID = orders.userID
GROUP BY customers.userID, customers.username

answered Jan 25, 2013 at 15:58

Kickstart

21.5k2 gold badges26 silver badges33 bronze badges

Collectives™ on Stack Overflow

SQL query within another query

4 Answers 4

Comments

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related