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

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.

CodePudding user response：

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);

CodePudding user response：

Just another option (assuming [Type] is a bit) and [Date] is a date) you can use a simple average

Example

Select YearMonth = left(Date,7)
      ,Pct = avg( convert(float,[Type]) )
 From YourTable
 Group By left(Date,7)

Results

YearMonth   Pct
2013-01     0.5
2013-02     1