Get two percentages in a single query

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
recci
Forum Commoner
Posts: 42
Joined: Tue Jul 29, 2008 10:01 pm

Get two percentages in a single query

Post by recci »

Ok I have a single table called opportunities, within that table I have field called sales_stage. I need to get the percentage of rows where sales_stage is "Closed Won" and also get percentage of rows where sales_stage is "Closed Lost" And basically just display the two headings Closed won and Closed Lost.

I'm a little bit lost with how to do this one, here is an sql fiddle

http://sqlfiddle.com/#!2/ac28d/13
User avatar
Weirdan
Moderator
Posts: 5978
Joined: Mon Nov 03, 2003 6:13 pm
Location: Odessa, Ukraine

Re: Get two percentages in a single query

Post by Weirdan »

This is called 'pivot': http://en.wikibooks.org/wiki/MySQL/Pivot_table

What you need is, basically:

Code: Select all

select 
   sum(sales_stage="Closed Won")/count(*) * 100 as won_pct, 
   sum(sales_stage="Closed Lost")/count(*) * 100 as lost_pct
from opportunities
recci
Forum Commoner
Posts: 42
Joined: Tue Jul 29, 2008 10:01 pm

Re: Get two percentages in a single query

Post by recci »

Thanks that seems to be exaclty what I was looking for
Post Reply