CODEWITHSUNDEEP
sql-serverdategroup-byssms
ChazC
ChazC

Reputation: 61

SQL query to return percentage of rows where binary variable equals a value, by month

I need to run a query that returns the % of rows where the Type column is equal to "Yes", grouped by month. My data looks similar to what is below

id Date Type
1 1/1/2013 Yes
2 1/2/2013 No
3 2/1/2013 Yes
4 2/2/2013 Yes

So, I'd want to return something like

Month Percentage
1/2013 0.5
2/2013 1

I tried this based on a previous answer, but since my 'Type' column isn't numeric it doesn't work.

SELECT  DATEPART(YEAR,Date) AS Year,
        DATEPART(MONTH,Date) AS Month,
        SUM(CAST(Type AS INT)) AS Passes,
        COUNT(*) as Total,
        CAST(1.00*SUM(CAST(Type AS INT)) / COUNT(*) AS DECIMAL(5,2) AS PercentagePass
FROM table
GROUP BY DATEPART(YEAR,Date), DATEPART(MONTH,Date)
ORDER BY DATEPART(YEAR,Date), DATEPART(MONTH,Date)

After some searching around I'm a bit confused on how to best handle my 'Type' variable as a string. I know I can convert it to 1s and 0s, but feel that replacing a line or two from the code above would be more efficient. I just can't seem to figure out how.

Upvotes: 0

Views: 250

Answers (2)

John Cappelletti
John Cappelletti

Reputation: 82010

Just another option ... you can use a simple average

Example

Select YearMonth = left(Date,7)
      ,Pct = avg( case when [Type]='Yes' then 1.0 else 0.0 end)
 From YourTable
 Group By left(Date,7)

Results

YearMonth   Pct
2013-01     0.5
2013-02     1

Upvotes: 1

Stu
Stu

Reputation: 32614

It looks like you're just having trouble with Type, just use a case expression or inline if such as:

SELECT  DATEPART(YEAR,Date) AS Year,
        DATEPART(MONTH,Date) AS Month,
        SUM(Iif(type='Yes',1,0)) AS Passes,
        COUNT(*) as Total,
        Cast(SUM(Iif(type='Yes',1.0,0)) / COUNT(*) AS DECIMAL(5,2)) AS PercentagePass
FROM t
GROUP BY DATEPART(YEAR,Date), DATEPART(MONTH,Date)
ORDER BY DATEPART(YEAR,Date), DATEPART(MONTH,Date);

Upvotes: 1

Related Questions