CODEWITHSUNDEEP
sqlpostgresql
Jake Wagner
Jake Wagner

Reputation: 826

Divide in SQL with a where clause

I need to divide where Salesman is Yes and Product is Pen over the total amount of sales (in my case is 8) and get the total percentage.

select * from mytable   

Sale_Number      Date            Salesman_Used           Product

 1            01/01/2014             No                    Pen
 2            01/02/2014             No                    Pen
 3            01/03/2014             Yes                   Pen
 4            01/04/2014             Yes                   Pencil
 5            01/05/2014             Yes                   Pen
 6            01/06/2014             Yes                   Pencil  
 7            01/07/2014             No                    Pencil
 8            01/08/2014             No                    Pencil

I am stuck on the missing piece:

select concat(100 * count(missing this piece) / count(Sale_Number), '%') 
as "Salesman Sales for Pens"
from mytable
where Salesman_Used = 'Yes' 
and Product = 'Pen'

The problem with my attempt is that the where clause affects the Sale_Number as well.. How can I do this? Thanks in advance.

Upvotes: 1

Views: 1078

Answers (4)

Abelisto
Abelisto

Reputation: 15624

There is filter clause for an aggregates:

If FILTER is specified, then only the input rows for which the filter_clause evaluates to true are fed to the aggregate function; other rows are discarded.

So:

select concat(100 * count(*) filter (where Salesman_Used = 'Yes' and Product = 'Pen') / count(Sale_Number), '%') 
as "Salesman Sales for Pens"
from mytable

Upvotes: 2

Jeremy Real
Jeremy Real

Reputation: 766

Try this:

select concat(100 * count(missing this piece) / count(select Sale_Number from mytable), '%') 
as "Salesman Sales for Pens"
from mytable
where Salesman_Used = 'Yes' 
and Product = 'Pen'

Upvotes: 2

Stephan Lechner
Stephan Lechner

Reputation: 35164

You could use a subquery to get the overall count of sales entries:

select concat(100 * count(*) / (select count(*) from mytable), '%') 
as "Salesman Sales for Pens"
from mytable
where Salesman_Used = 'Yes' 
and Product = 'Pen'

Upvotes: 2

Carlos
Carlos

Reputation: 1822

I have never used Postgresql but maybe this works:

select concat(100 * (select count(*) from mytable) / count(Sale_Number), '%') 
as "Salesman Sales for Pens"
from mytable
where Salesman_Used = 'Yes' 
and Product = 'Pen'

Upvotes: 1

Related Questions