Show all patients grouped into weight groups. Show the total amount of patients in each weight group-CodePudding

Show all of the patients grouped into weight groups. Show the total amount of patients in each weight group. Order the list by the weight group descending.

For example, if they weight 100 to 109 they are placed in the 100 weight group, 110-119 = 110 weight group, etc.

CodePudding user response：

So something like this?

SELECT FLOOR(t.weight/10) * 10 as weightGroup, count(*) as cnt
FROM YourTable t
GROUP BY FLOOR(t.weight/10)
ORDER BY FLOOR(t.weight/10) DESC

CodePudding user response：

I tend to try and avoid expensive floating point functions like FLOOR() or CEIL().

A trick to FLOOR() a number to the next lower integer is to add 0.5 to it and hard-cast it to a signed integer using CONVERT().

WITH
--- some sample data, don't use in final query ...
indata(id,lb) AS (
          SELECT  1,101
UNION ALL SELECT  2,102 
UNION ALL SELECT  3,103 
UNION ALL SELECT  4,104 
UNION ALL SELECT  5,105 
UNION ALL SELECT  6,106 
UNION ALL SELECT  7,107 
UNION ALL SELECT  8,108 
UNION ALL SELECT  9,109 
UNION ALL SELECT 10,110 
UNION ALL SELECT 11,111 
UNION ALL SELECT 12,112 
UNION ALL SELECT 13,113 
UNION ALL SELECT 14,114 
UNION ALL SELECT 15,115 
UNION ALL SELECT 16,116 
UNION ALL SELECT 17,117 
UNION ALL SELECT 18,118 
UNION ALL SELECT 19,119 
UNION ALL SELECT 20,120 
)
-- real query starts here ...
SELECT
  CONVERT ((lb  / 10 - 0.5), SIGNED)  AS grp 
, COUNT(*) AS pat_count
FROM indata
GROUP BY grp
ORDER BY grp DESC;
-- out  grp | patcount 
-- out ----- ----------
-- out   12 |        1
-- out   11 |       10
-- out   10 |        9