Home » SQL & PL/SQL » SQL & PL/SQL » GROUP BY AND BETWEEN
GROUP BY AND BETWEEN [message #195822] Mon, 02 October 2006 11:51 Go to next message
superoscarin@hotmail.com
Messages: 101
Registered: July 2006
Location: Mexico
Senior Member

Hi:

I have a SQL question:

I have a table with the follow data:

id name age
1 fred 26
2 scott 18
3 megan 35

I need to do a group by defining age range like
0-10
11-20
21-30

but if i do it using where age between 0 and 10; the records with age bigger than 10 will be excluded. i need some thing like this

age count
0-10 0
11-20 1
21-30 1
31-40 1

Records gruop by age but without exlude any record. I believe that if i use having i will get some portion of data and not all of them. And if i use group by age my range will by age by age and i don't want that.

Thanks
Alex
Re: GROUP BY AND BETWEEN [message #195827 is a reply to message #195822] Mon, 02 October 2006 11:59 Go to previous messageGo to next message
Cthulhu
Messages: 381
Registered: September 2006
Location: UK
Senior Member
I hope you're not really storing ages or you will be spending your life updating the table to keep it accurate !

Anyway, your query could be done using CASE statements:

select count(case when age between 0 and 10 then 1 end),
       count(case when age between 11 and 20 then 1 end),
       count(case when age between 21 and 30 then 1 end),
       count(case when age between 31 and 40 then 1 end)
from table

[Updated on: Mon, 02 October 2006 12:00]

Report message to a moderator

Re: GROUP BY AND BETWEEN [message #195829 is a reply to message #195827] Mon, 02 October 2006 12:10 Go to previous messageGo to next message
superoscarin@hotmail.com
Messages: 101
Registered: July 2006
Location: Mexico
Senior Member

yes, you are right...

i'm have the birthday then i will try with sysdate - birthday

I believe that there are something like (years between) to get only the years and then use your recommendation...

One more question...


what does "then 1 end)" means??

żIs it posible to have nested group by??

Thanks & greetings
alex

[Updated on: Mon, 02 October 2006 14:15]

Report message to a moderator

Re: GROUP BY AND BETWEEN [message #195858 is a reply to message #195829] Mon, 02 October 2006 16:25 Go to previous messageGo to next message
superoscarin@hotmail.com
Messages: 101
Registered: July 2006
Location: Mexico
Senior Member

this is my query

SELECT BO.BASE_ORIGEN, TRUNC(MONTHS_BETWEEN(SYSDATE , FECHA_NACIMIENTO)/12) AS EDAD, FIS.SEXO, RET.RETENEDOR, UP.UNIDAD_PAGO , COUNT (FIS.ID_PERSONA)
FROM DM08_FISICA FIS
JOIN DM08_POLIZA POL
ON FIS.ID_PERSONA = POL.ID_PERSONA
JOIN DM08_DATOS_TRABAJO DT
ON POL.ID_PERSONA = DT.ID_PERSONA
JOIN DM08_RETENEDOR RET
ON DT.ID_RETENEDOR = RET.ID_RETENEDOR
JOIN DM08_UNIDAD_PAGO UP
ON DT.ID_UNIDAD_PAGO = UP.ID_UNIDAD_PAGO
JOIN DM08_PERSONA PER
ON FIS.ID_PERSONA = PER.ID_PERSONA
JOIN DM08_BASE_ORIGEN BO
ON PER.ID_BASE_ORIGEN = BO.ID_BASE_ORIGEN
GROUP BY BO.BASE_ORIGEN, (TRUNC(MONTHS_BETWEEN(SYSDATE , FECHA_NACIMIENTO)/12)), FIS.SEXO, RET.RETENEDOR, UP.UNIDAD_PAGO

My results are:

BASE_ORIGEN EDAD S RETENEDOR UNIDAD_PAGO COUNT(FIS.ID_PERSONA)
IFAI 12 ANALISIS Y RIESGO COMISION NACIONAL BANCARIA Y DE VALORES 1
IFAI 17 ANALISIS Y RIESGO COMISION NACIONAL BANCARIA Y DE VALORES 1
IFAI 25 ANALISIS Y RIESGO COMISION NACIONAL BANCARIA Y DE VALORES 1
IFAI 26 ANALISIS Y RIESGO COMISION NACIONAL BANCARIA Y DE VALORES 1
IFAI 27 ANALISIS Y RIESGO COMISION NACIONAL BANCARIA Y DE VALORES 1
IFAI 30 ANALISIS Y RIESGO COMISION NACIONAL BANCARIA Y DE VALORES 1
IFAI 38 ANALISIS Y RIESGO COMISION NACIONAL BANCARIA Y DE VALORES 1
IFAI 40 ANALISIS Y RIESGO COMISION NACIONAL BANCARIA Y DE VALORES 1
IFAI 43 ANALISIS Y RIESGO COMISION NACIONAL BANCARIA Y DE VALORES 3
IFAI 44 ANALISIS Y RIESGO COMISION NACIONAL BANCARIA Y DE VALORES 1
IFAI 46 ANALISIS Y RIESGO COMISION NACIONAL BANCARIA Y DE VALORES 1
IFAI 51 ANALISIS Y RIESGO COMISION NACIONAL BANCARIA Y DE VALORES 1
IFAI 56 ANALISIS Y RIESGO COMISION NACIONAL BANCARIA Y DE VALORES 2
IFAI 65 ANALISIS Y RIESGO COMISION NACIONAL BANCARIA Y DE VALORES 1
IFAI 72 ANALISIS Y RIESGO COMISION NACIONAL BANCARIA Y DE VALORES 1
IFAI 78 ANALISIS Y RIESGO COMISION NACIONAL BANCARIA Y DE VALORES 1
IFAI 91 ANALISIS Y RIESGO COMISION NACIONAL BANCARIA Y DE VALORES 1
IFAI 104 ANALISIS Y RIESGO COMISION NACIONAL BANCARIA Y DE VALORES 1


and your answer was :

SELECT COUNT (CASE WHEN TRUNC(MONTHS_BETWEEN(SYSDATE , FECHA_NACIMIENTO)/12) BETWEEN 0 AND 5 THEN 1 END) AS "<5",
COUNT (CASE WHEN TRUNC(MONTHS_BETWEEN(SYSDATE , FECHA_NACIMIENTO)/12) BETWEEN 6 AND 10 THEN 1 END) AS "6-10",
COUNT (CASE WHEN TRUNC(MONTHS_BETWEEN(SYSDATE , FECHA_NACIMIENTO)/12) BETWEEN 11 AND 15 THEN 1 END)AS "11-15",
COUNT (CASE WHEN TRUNC(MONTHS_BETWEEN(SYSDATE , FECHA_NACIMIENTO)/12) BETWEEN 16 AND 20 THEN 1 END) AS "16-20",
COUNT (CASE WHEN TRUNC(MONTHS_BETWEEN(SYSDATE , FECHA_NACIMIENTO)/12) BETWEEN 21 AND 25 THEN 1 END) AS "21-25",
COUNT (CASE WHEN TRUNC(MONTHS_BETWEEN(SYSDATE , FECHA_NACIMIENTO)/12) BETWEEN 26 AND 30 THEN 1 END)AS "26-30",
COUNT (CASE WHEN TRUNC(MONTHS_BETWEEN(SYSDATE , FECHA_NACIMIENTO)/12) BETWEEN 31 AND 35 THEN 1 END) AS "31-35",
COUNT (CASE WHEN TRUNC(MONTHS_BETWEEN(SYSDATE , FECHA_NACIMIENTO)/12) BETWEEN 36 AND 40 THEN 1 END) AS "36-40",
COUNT (CASE WHEN TRUNC(MONTHS_BETWEEN(SYSDATE , FECHA_NACIMIENTO)/12) BETWEEN 41 AND 45 THEN 1 END)AS "41-45",
COUNT (CASE WHEN TRUNC(MONTHS_BETWEEN(SYSDATE , FECHA_NACIMIENTO)/12) BETWEEN 46 AND 50 THEN 1 END) AS "46-50",
COUNT (CASE WHEN TRUNC(MONTHS_BETWEEN(SYSDATE , FECHA_NACIMIENTO)/12) BETWEEN 51 AND 55 THEN 1 END) AS "51-55",
COUNT (CASE WHEN TRUNC(MONTHS_BETWEEN(SYSDATE , FECHA_NACIMIENTO)/12) BETWEEN 56 AND 60 THEN 1 END)AS "56-60",
COUNT (CASE WHEN TRUNC(MONTHS_BETWEEN(SYSDATE , FECHA_NACIMIENTO)/12) BETWEEN 61 AND 65 THEN 1 END) AS "60-65",
COUNT (CASE WHEN TRUNC(MONTHS_BETWEEN(SYSDATE , FECHA_NACIMIENTO)/12) > 65 THEN 1 END) AS ">65"
FROM DM08_FISICA

and my results are:

<5 6-10 11-15 16-20 21-25 26-30 31-35 36-40 41-45 46-50 51-55 56-60 60-65 >65
0 0 1 1 1 3 0 2 4 1 1 2 1 4

And i dont know how to match the results of my second query against the column age (edad) in the first query. Can anybody helpme?? I think that i need another group by.... for my column edad Is it posible??

Thanks Alex

[Updated on: Mon, 02 October 2006 16:26]

Report message to a moderator

Re: GROUP BY AND BETWEEN [message #195923 is a reply to message #195822] Tue, 03 October 2006 03:10 Go to previous messageGo to next message
Cthulhu
Messages: 381
Registered: September 2006
Location: UK
Senior Member
Divide the months_between figure by 12 to convert to years ?
Re: GROUP BY AND BETWEEN [message #196270 is a reply to message #195923] Wed, 04 October 2006 13:00 Go to previous messageGo to next message
superoscarin@hotmail.com
Messages: 101
Registered: July 2006
Location: Mexico
Senior Member

Yes i got the difference in months to get the years.

If i had 40 months / 12 i should get 3 years 4 moths...

Alex
Re: GROUP BY AND BETWEEN [message #196313 is a reply to message #196270] Thu, 05 October 2006 00:09 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
look in the docs for the MOD function. Combine this with the division by 12.
Re: GROUP BY AND BETWEEN [message #196471 is a reply to message #195822] Thu, 05 October 2006 08:35 Go to previous messageGo to next message
rameshuddaraju
Messages: 69
Registered: June 2005
Location: India
Member

SELECT agebet || ' - ' || agebet + 10 age_range ,frequence
FROM (SELECT FLOOR(age / 10) * 10 agebet,COUNT(*) frequence FROM yourtable GROUP BY FLOOR(age / 10) * 10)
Re: GROUP BY AND BETWEEN [message #196473 is a reply to message #196471] Thu, 05 October 2006 08:37 Go to previous message
rameshuddaraju
Messages: 69
Registered: June 2005
Location: India
Member

Sorry it is wrongly posted in this.
Previous Topic: Update query Help
Next Topic: Toms CSV export program
Goto Forum:
  


Current Time: Tue Dec 06 15:56:36 CST 2016

Total time taken to generate the page: 0.14100 seconds