CODEWITHSUNDEEP
mysql
Chris
Chris

Reputation: 4728

SQL query within another query

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

Upvotes: 0

Views: 147

Answers (4)

Kickstart
Kickstart

Reputation: 21513

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

Upvotes: 0

juergen d
juergen d

Reputation: 204766

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

Upvotes: 0

Derek
Derek

Reputation: 23228

you're close...

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

Upvotes: 0

John Woo
John Woo

Reputation: 263723

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,

Upvotes: 1

Related Questions