March 26, 2018 at 1:00 pm
I am trying to figure out how to pull the data from two tables where I can return firstname, lastname, item, price and the % of total price for each customer.
Two tables:
Customers (customerid, firstname, lastname, city, state)
Items_ordered ( customerid,ordered_date,item,quantity,price)
This statement will only return the first customer. How can you get a SQL statement that will go row by row and return all the information?
SELECT firstname, lastname, price, price/SUM(price)
FROM customers C
JOIN items_ordered O ON (C.customerid=O.customerid);
March 26, 2018 at 1:45 pm
jarodjp42 - Monday, March 26, 2018 1:00 PMI am trying to figure out how to pull the data from two tables where I can return firstname, lastname, item, price and the % of total price for each customer.
Two tables:
Customers (customerid, firstname, lastname, city, state)
Items_ordered ( customerid,ordered_date,item,quantity,price)This statement will only return the first customer. How can you get a SQL statement that will go row by row and return all the information?
SELECT firstname, lastname, price, price/SUM(price)
FROM customers C
JOIN items_ordered O ON (C.customerid=O.customerid);
Is the sum based on all orders or the sum for the individual customers orders? I think you may be looking for something like one of these:
--based on all orders
SELECT firstname, lastname, price, price/(select sum(price) FROM items_ordered) as PercentOfTotal
FROM customers C
JOIN items_ordered O ON (C.customerid=O.customerid);
--based on customers orders
SELECT firstname, lastname, price, price/(select sum(price) FROM items_ordered where Customerid = C.CustomerID) as PercentOfTotal
FROM customers C
JOIN items_ordered O ON (C.customerid=O.customerid);
Sue
March 26, 2018 at 2:09 pm
Sue_H - Monday, March 26, 2018 1:45 PMjarodjp42 - Monday, March 26, 2018 1:00 PMI am trying to figure out how to pull the data from two tables where I can return firstname, lastname, item, price and the % of total price for each customer.
Two tables:
Customers (customerid, firstname, lastname, city, state)
Items_ordered ( customerid,ordered_date,item,quantity,price)This statement will only return the first customer. How can you get a SQL statement that will go row by row and return all the information?
SELECT firstname, lastname, price, price/SUM(price)
FROM customers C
JOIN items_ordered O ON (C.customerid=O.customerid);Is the sum based on all orders or the sum for the individual customers orders? I think you may be looking for something like one of these:
--based on all orders
SELECT firstname, lastname, price, price/(select sum(price) FROM items_ordered) as PercentOfTotal
FROM customers C
JOIN items_ordered O ON (C.customerid=O.customerid);--based on customers orders
SELECT firstname, lastname, price, price/(select sum(price) FROM items_ordered where Customerid = C.CustomerID) as PercentOfTotal
FROM customers C
JOIN items_ordered O ON (C.customerid=O.customerid);Sue
Thank you Sue, that is exactly what I was looking for. I couldn't figure out where to put the subquery. I was only able to pull the first data set. Thank you for your help!
March 26, 2018 at 2:30 pm
Can anyone give an explanation of why you put the subquery after the divided sign? I tried to putting it before the divided sign, like this but wasn't getting the result I wanted.
SELECT firstname, lastname, price, (SELECT price/SUM(price) FROM items_ordered)
FROM customers C
JOIN items_ordered O ON (C.customerid=O.customerid);
This is how it should be, thanks to SUE
SELECT firstname, lastname, price, price/(select sum(price) FROM items_ordered)
FROM customers C
JOIN items_ordered O ON (C.customerid=O.customerid);
If anyone can explain why you put the subquery after the divided sign that would help me better understand subqueries. Thanks!
June 10, 2018 at 11:36 pm
jarodjp42 - Monday, March 26, 2018 1:00 PM% of total price for each customer.
@jarodjp42:
Because you asked for % (percentage), that is why you needed SUM() after the division to get the percentage value.
=======================================================================
June 11, 2018 at 2:33 am
jarodjp42 - Monday, March 26, 2018 2:30 PMCan anyone give an explanation of why you put the subquery after the divided sign? I tried to putting it before the divided sign, like this but wasn't getting the result I wanted.SELECT firstname, lastname, price, (SELECT price/SUM(price) FROM items_ordered)
FROM customers C
JOIN items_ordered O ON (C.customerid=O.customerid);This is how it should be, thanks to SUE
SELECT firstname, lastname, price, price/(select sum(price) FROM items_ordered)
FROM customers C
JOIN items_ordered O ON (C.customerid=O.customerid);If anyone can explain why you put the subquery after the divided sign that would help me better understand subqueries. Thanks!
Because if you don't use sum under subquery then you have use group by function for all the non aggregated columns which makes you very clumsy/complex . Sue written it very elegantly.
Saravanan
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply