CODEWITHSUNDEEP
sql-serverselectpercentage
yeloTag yeloTag
yeloTag yeloTag

Reputation: 1

How to write sql select in order to obtain percentage

I define a view which contains 2 columns

France    Wine 
France    Fromage
France    Meat
France    Chips
Belgium   Bier
Belgium   Chips  
Belgium   Chocolate

i love Bier and Chips which select in order to obtain the best country like

Belgium 66%
France  25%

Upvotes: 0

Views: 890

Answers (2)

Ed Gibbs
Ed Gibbs

Reputation: 26333

You want the count for Bier and Chips for each country, divided by the total item count for each country.

The count for Bier and Chips goes something like this:

COUNT(CASE WHEN Item IN ('Bier', 'Chips') THEN 1 END)

If Item is Bier or Chips, the CASE returns 1, which is counted. If it's not Bier or Chips, the CASE returns a NULL, which is not counted.

Putting it all together:

SELECT
  Country,
      COUNT(CASE WHEN Item IN ('Bier', 'Chips') THEN 1 END) /
      CAST(COUNT(*) AS NUMERIC)
    AS BierChipsPercent
FROM myTable
GROUP BY Country

Why the CAST(COUNT(*) AS NUMERIC)? Because without it, SQL Server appears to be treating both COUNTs as integer so it's truncating the result when they're divided. Casting to NUMERIC makes it behave, so 1/4 will yield 0.25 instead of zero.

The above query will give you:

Belgium 0.6666666666
France  0.25

To get the percentage, just multiply by 100. To get whole numbers, apply the ROUND() function:

SELECT
  Country,
  ROUND(
      COUNT(CASE WHEN Item IN ('Bier', 'Chips') THEN 1 END) /
      CAST(COUNT(*) AS NUMERIC) * 100, 0)
    AS BierChipsPercent
FROM myTable
GROUP BY Country

This will give you:

Belgium 67
France  25

Note that Belgium's percentage rounds up to 67. If you want it to be 66, use FLOOR instead of ROUND.

There's a SQL Fiddle for this here.

Upvotes: 3

yeloTag yeloTag
yeloTag yeloTag

Reputation: 1

This is my select but I understood that I can also write with over() ???

SELECT  c1 ,
convert(
        decimal(5,2),
        100.00 * 
        (select COUNT(*) from myTable WHERE c1=t.c1 and (c2='Chips' or c2='Bier')) 
        /
        (SELECT count(*) FROM myTable WHERE c1=t.c1)
        ) AS percentage

  FROM myTable t
  group by c1

 c1         percentage
 Belgium    66.67
 France     25.00

Upvotes: 0

Related Questions