Blake Li February 2016

SQLite Calculate sum outside group by

I'm trying to calculate the percentage of a customer has spent over the total sales value. I have calculated the total sales value per customer using sum() and group by, but after I use group by, I cannot differentiate the total sales value and the individual total for each sustomer.

is there anyway i could get around this?

i got to here so far and dont know what to do next:

select c.firstname ||' '|| c.lastname as 'Ful name', 
sum(total) as 'Sales value', 
/*something to calculate percentage*/,
from invoice i inner join customer c on i.customerid = c.customerid 
group by i.customerid order by sum(total) desc limit 5;

Answers


CL. February 2016

To calculate the simple sum over the entire table, move it into an independent subquery:

SELECT ...,
       sum(total) / (SELECT sum(total) FROM invoice)
FROM ...;

Post Status

Asked in February 2016
Viewed 1,084 times
Voted 7
Answered 1 times

Search




Leave an answer