CODEWITHSUNDEEP
sqlhive
Rik Johnson
Rik Johnson

Reputation: 111

How to divide the sum of a column under two different conditions?

Say I have three columns, one for the date, one for the values, and one for some category. I can get the sum of the values grouped by the date with the following code

SELECT date, SUM(values) from table WHERE category = condition GROUP BY date

And I can get the sum of all the values with 

SELECT date, SUM(values) from table GROUP BY date

I want the values for the SUM(values) column to be equal to the (SUM(values) where category = condition) / (SUM(values)), or in plain words, the proportion of the values with that condition. Is there a way to do this in one query? Thank you!

Upvotes: 0

Views: 844

Answers (1)

Vinit
Vinit

Reputation: 2607

You may use CASE condition inside aggregate functions like SUM.

SELECT 
date, 
(
  SUM(CASE WHEN category = condition Then values ELSE 0)
  / SUM(Values)
) as proportion
from table 
GROUP BY date

Upvotes: 1

Related Questions