Counting records in a range as a group. 1

[basildon]

Hi

If I have a field e.g. age, and I wish to count records that are part of a group I have defined (e.g. age 20-40, 40-60,60+) how can I do this in MySql SQL?

I have DuBois book, but can't find solution.

Thanks

 

[Broccoli2]

Try one of these methods:

Method 1:
Here the totals end up in columns. Convenient if you don't need to change the age bands often.

SELECT
SUM(IF((age>20) AND (age<=40),1,0)) AS g20to40,
SUM(IF((age>40) AND (age<=60),1,0)) AS g40to60,
SUM(IF((age>60),1,0)) AS g60plus
FROM table;

Method 2:
Data ends up as rows. However, this SQL statement is even trickier to maintain if you need to change things.

SELECT
IF(age<20,'Below20',
  IF(age<40,'g20to40',
    IF(age<60,'g40to60','Over60')
  )
) AS AgeBand,
Count(PrimaryKey) AS NumPeople
FROM Table
GROUP BY AgeBand;

Method 3:
Use a separate table

agebands(AgeBandID,Lower,Upper,Description)

. Again data ends up in rows. Although requiring a bit more effort to begin with, it's much easier to work with in the end.

SELECT
Description, Count(table.Age)
FROM table, agebands
WHERE table.Age>agebands.Lower AND table.age<agebands.Upper
GROUP BY Description;

Hope this helps

-Rob

 

[basildon]

Rob

Thanks for your help.

I'm using DBI and entered the two (logical sounding) examples below, however DBD::MySQL doesn't seem to like it and won't execute.

There doesn't seem to be anything in DBD::MySQL documentation to suggest this won't work.

If anyone has any ideas I'd really appreciate it as at the moment I'm having to run them as seperate queries,

Thanks



SELECT
IF(sic87 < 3000, 'g1',
IF(sic87 < 6000, 'g2',
IF(sic87 < 9000, 'g3')))
AS bandshown
from GLOBAL1
GROUP BY bandshown;


SELECT
SUM (IF((sic87 > 0) AND (sic87 <= 2999),1,0)) AS g1-4,
SUM (IF((sic87 > 2999) AND (sic87 <= 5999),1,0)) AS g4-7,
SUM (IF((sic87 > 5999) AND (sic87 <= 8999),1,0)) AS g7-9
from GLOBAL1;

 

[Broccoli2]

I'm not sure about DBI, but the third

IF

function in your first SQL statement needs a third argument (for the case sic87>9000) - it could also do with a

Count(sic87)

column if you want the totals. Can't spot any errors in your second SQL statement though - perhaps it's something to do with how the SQL command is being passed to the server. You could check this by connecting directly using the mysql console client and typing in the SQL to see if you still get an error.

-Rob