Help with percentages query please 1

[Rene1024]

Hello,

I have a table that contains a column for city and another column that contains yes and no data.
How can I determine the percentages of yes and no and group them by city?

Rgds.

Rene

 

[bborissov]

SELECT City,
       SUM(CASE WHEN Column = 'yes'
                THEN 1
                ELSE 0 END)*100.0/COUNT(*) AS YesPrc,       SUM(CASE WHEN Column = 'No'
                THEN 1
                ELSE 0 END)*100.0/COUNT(*) AS NoPrc
FROM YourTable
GROUP BY City

not tested

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.
Microsoft MVP VFP

 

[Rene1024]

Perfect.

Thank you!

Rgds.

Rene

 

[Rene1024]

Borislav,

Thanks again for your help, how can I display the number of yes and no groupped by city?
I tried to figure it out from your previous post, but my sql is very weak.

Rgds.
Rene

 

[bborissov]

The same way:

SELECT City,
       SUM(CASE WHEN Column = 'yes'
                THEN 1
                ELSE 0 END)*100.0/COUNT(*) AS YesPrc,       
       SUM(CASE WHEN Column = 'yes'
                THEN 1
                ELSE 0 END)               AS NumberYesPrc,      
       SUM(CASE WHEN Column = 'No'
                THEN 1
                ELSE 0 END)*100.0/COUNT(*) AS NoPrc,
       SUM(CASE WHEN Column = 'No'
                THEN 1
                ELSE 0 END)               AS NumberNoPrc
FROM YourTable
GROUP BY City

not testes

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.
Microsoft MVP VFP

 

[Rene1024]

Thank you again for taking the time to reply to my messages.

Sincerely,

Rene