CODEWITHSUNDEEP
mysqlcountlogicpercentage
Karaelfte
Karaelfte

Reputation: 143

COUNT twice the same column

I'm trying to count twice the same column with different conditions to at the end make a calcul to know a certain percentage.

The column is filled with several numbers from 1 to 10 and I would like to know the percentage of row where the number is superior to 6.

I had that idea to retrieve the numbers :

SELECT number, Sum(number> 6) as number_sup_to_6 FROM table;

But I would like after retrieving those to calculate the percentage of numbers superior to six to the numbers in total : ((n>6)/n)*100

I then took inspiration from a previous post to make the calcul :

concat(round(( number_sup_to_6 /number* 100 ),2),'%') AS percentage

Problem is, I don't know how to put those 2 pieces of sql together, could someone please help me how to understand how to combine them together ?

Thank you for your help.

Upvotes: 1

Views: 1360

Answers (3)

spencer7593
spencer7593

Reputation: 108430

I suggest this alternative for conditional aggregation: 

SELECT SUM(t.number>6) / COUNT(t.number) AS ratio_num_sup_to_six
  FROM mytable t

Note that this counts the rows that have a number value greater than six, and counts the rows that have non-null values of number. This is equivalent to the result returned by:

SELECT COUNT(IF(t.number>6,1,NULL)) / COUNT(t.number) AS ratio_num_sup_to_six
  FROM mytable t

We can also express that in a more ANSI-standards compliant equivalent:

SELECT COUNT(CASE WHEN t.number>6 THEN 1 ELSE NULL END) 
     / COUNT(t.number) AS ratio_num_sup_to_six
  FROM mytable t

If the goal is to get a total from the values stored in the number column (a SUM of the values rather than a COUNT of the rows), then something like this:

SELECT SUM(IF(t.number>6,t.number,NULL)) / SUM(t.number) AS ratio_tot_sup_to_six
  FROM mytable t

Formatting

If we absolutely need to do the conversion from the ratio to a percentage for display in the SQL query itself (rather than the client), then we can wrap any of the expressions above in:

  CONCAT(ROUND(( expr )*100,2),'%')

For example:

SELECT CONCAT(ROUND(( SUM(t.number>6) / COUNT(t.number) )*100,2),'%') AS pct_num_sup_to_six
  FROM mytable t

Note that the return from the ROUND function is not guaranteed to include two digits after the decimal point. That is, we might get '22.2%' or '50%' returned.

If it's important to return two digits after the decimal point (for display), we can use the MySQL FORMAT function https://dev.mysql.com/doc/refman/5.7/en/string-functions.html#function_format

This will guarantee that two (or however many we want) decimal digits are returned, even when the digits are zero. So we can get '50.00' returned rather than '50'. (The bother with FORMAT is that it will also include comma separators for values >= 1000.)

Performance

This approach will be a single pass through the table, more efficient than other solutions that impose the extra overhead of materializing inline views (derived tables).

Upvotes: 1

Ashutosh SIngh
Ashutosh SIngh

Reputation: 1019

Use CASE statement 

SELECT number, (number_sup_to_6/total_count)*100 AS percentage
FROM
(SELECT number, 
COUNT(CASE WHEN number > 6 THEN number ELSE NULL END) AS number_sup_to_6, 
COUNT(number) AS total_count FROM table_name 
GROUP BY number) AS a

Upvotes: 2

Darshan Mehta
Darshan Mehta

Reputation: 30829

Try this:

SELECT CONCAT(ROUND(( a.sum / b.sum * 100 ), 2), '%') AS percentage
FROM 
(SELECT SUM(number) AS sum FROM table WHERE number > 6) a,
(SELECT SUM(number) AS sum FROM table) b

Upvotes: 1

Related Questions