CODEWITHSUNDEEP
sqlsql-serverselectpercentage
Mr A
Mr A

Reputation: 6778

Find percentage of products against total customers

I have got sql table which has 2 columns userid and products, userid is unique while products will have 3 values[0,1,-1] -1 represents no products bought, 1 represents product currently being used, while 0 represents product has been used.

What i want is a percentage query for total users in the table
product used (%): ?
products currently in use (%): ?
product not sold(%): ?

Any suggestion or assistance will be highly appreciated Thanks

enter image description here
What I have tried so far?

Select count(userId) * 100 / (select count(products) Where products = 1) AS perc 
from product group by userid

Upvotes: 1

Views: 594

Answers (4)

jinesh ar
jinesh ar

Reputation: 13

SELECT products,
(convert(numeric(5,2),count(products))/(SELECT convert(numeric(5,2),count(products)) FROM product)) * 100
AS "Percentage"
FROM product GROUP BY products

Upvotes: 0

Pradeeshnarayan
Pradeeshnarayan

Reputation: 1235

You can try like this also.

FIDDLE

select count(*) total
,count(p1.products)/count(*)*100 as currentlyused
,count(p2.products)/count(*)*100 as used
,count(p3.products)/count(*)*100 as not_sold

from products p
left join products p1 on p1.products=1 and p1.id=p.id
left join products p2 on p2.products=0 and p2.id=p.id
left join products p3 on p3.products=-1  and p3.id=p.id

Upvotes: 0

David Faber
David Faber

Reputation: 12485

SELECT SUM(CASE WHEN Products = 1 THEN 1 ELSE 0 END) * 100 
       / COUNT(DISTINCT UserID) AS product_currently_in_use_pct
     , SUM(CASE WHEN Products = 0 THEN 1 ELSE 0 END) * 100 
        / COUNT(DISTINCT UserID) AS product_used_pct
     , SUM(CASE WHEN Products = -1 THEN 1 ELSE 0 END) * 100 
       / COUNT(DISTINCT UserID) AS product_not_sold_pct
  FROM MyTable

Upvotes: 1

András Ottó
András Ottó

Reputation: 7695

Try this:

SELECT SUM(CASE WHEN products = 1 THEN 1 ELSE 0 END) * 100   /
Count(DISTINCT Products.UserID) AS 'Currently used products%',
SUM(CASE WHEN products = 0 THEN 1 ELSE 0 END) * 100   /
Count(DISTINCT Products.UserID) As 'Used products%',
SUM(CASE WHEN products = -1 THEN 1 ELSE 0 END) * 100   /
Count(DISTINCT Products.UserID) AS 'Not sold products%'
FROM Products

Upvotes: 4

Related Questions