[mysql] group by

[marty]

stel je heb een tabel met de volgende data

code:

------------------------------
naam    leeftijd     rijbewijs
------------------------------
jan 18      ja
karel   20      nee
kees    20      ja
piet    21      nee
japie   19      nee
hans    19      nee
peter   23      ja
------------------------------

=> nu wil ik een query doen die alleen rows teruggeeft van:
- records waar dezelfde leeftijd meer dan 1x voorkomt
- EN waar bij allemaal het rijbewijs op nee staat
(leeftijd 19 dus in dit geval)

het eerste gedeelte kom ik wel uit:

MySQL:

SELECT leeftijd, COUNT(leeftijd) AS total FROM table
GROUP BY leeftijd HAVING total >= 2
ORDER BY total DESC

ik heb geprobeerd er zoiets van te maken:

MySQL:

SELECT leeftijd, COUNT(leeftijd) AS total FROM table
GROUP BY leeftijd HAVING total >= 2 AND rijbewijs='nee'
ORDER BY total DESC

maar dan krijg ik naast 19 ook 20 terug en da's niet de bedoeling

is dit mogelijk met 1 query?

edit:
(oh, en ik draai versie 3.23)

[DeverauX]

Having is te gebruiken om condities te stellen aan aggregated functies zoals SUM()/Count() etc, je kan dit dus niet gebruiken om de resultaten van de query zelf te beinvloeden, hiervoor kan je een WHERE clausule op rijbewijs gebruiken

[marty]

dit is dus wat jij voorstelt:

MySQL:

SELECT leeftijd, COUNT(leeftijd) AS total FROM table
WHERE rijbewijs='nee'
GROUP BY leeftijd HAVING total >= 2
ORDER BY total DESC

Dat werkt ook niet: stel dat er van 1 leeftijd 10 records zijn waarvan er maar eentje een rijbewijs heeft, dan zal die query zoals jij 'm voorstelt gewoon de overige 9 teruggeven. Maar dat wil ik niet - ik wil dat die helemaal niet in m'n resultset voorkomen.
Het gaat me dus alleen om de leeftijden waarbij niemand een rijbewijs heeft (en waarbij total >= 2 dus)

[_Sunnyboy_]

Werkt dit niet?

code:

SELECT 
leeftijd, 
COUNT(leeftijd) AS total,
SUM(IF(rijbewijs='ja',1,0)) AS iemand_rijbewijs 
FROM table
GROUP BY leeftijd 
HAVING total >= 2 AND iemand_rijbewijs=0
ORDER BY total DESC

[marty]

_Sunnyboy_ schreef op 17 September 2003 @ 11:54:
Werkt dit niet?

code:

SELECT 
leeftijd, 
COUNT(leeftijd) AS total,
SUM(IF(rijbewijs='ja',1,0)) AS iemand_rijbewijs 
FROM table
GROUP BY leeftijd 
HAVING total >= 2 AND iemand_rijbewijs=0
ORDER BY total DESC

Ja, dat werkt!

Je moet er maar opkomen Thnx!

[Riegstar]

Dit zou het moeten zijn:

code:

SELECT     leeftijd, COUNT(naam) AS total
FROM         TEST
WHERE     (rijbewijs = 'nee')
GROUP BY leeftijd
HAVING      (COUNT(naam) >= 2)

Helaas 2x COUNT, maar dat kan volgens mijn niet anders.
Tenminste in MSSQL mag het volgende niet:

code:

SELECT     leeftijd, COUNT(naam) AS total
FROM         TEST
WHERE     (rijbewijs = 'nee')
GROUP BY leeftijd
HAVING      (total >= 2)

[marty]

Riegstar schreef op 17 september 2003 @ 12:17:
Dit zou het moeten zijn:
[...]

Zoals ik al eerder aangaf is dat dus niet de oplossing

Helaas 2x COUNT, maar dat kan volgens mijn niet anders.
Tenminste in MSSQL mag het volgende niet:

[...]

maar ik werk met mysql, en daar mag het wel Maar het is dus - zoals gezegd - niet de oplossing voor het probleem dat ik had. (maar die is dus inmiddels al wel gegeven)

[slm]

marty schreef op 17 September 2003 @ 12:36:
[...]

Zoals ik al eerder aangaf is dat dus niet de oplossing


[...]

maar ik werk met mysql, en daar mag het wel Maar het is dus - zoals gezegd - niet de oplossing voor het probleem dat ik had. (maar die is dus inmiddels al wel gegeven)

Erhm.. volgens mij is wat riegstar aangeeft wél de oplossing en een beduidend nettere query bovendien. Je krijgt op die query alleen 19 terug met als totaal 2.

[marty]

slm schreef op 17 September 2003 @ 12:40:
[...]


Erhm.. volgens mij is wat riegstar aangeeft wél de oplossing en een beduidend nettere query bovendien. Je krijgt op die query alleen 19 terug met als totaal 2.

ik zou zeggen: probeer het maar!
Had ik namelijk ook al gedaan en gaf niet het gewenste resultaat, hij doet namelijk eerst de WHERE en gaat daarna pas groeperen. en het moet net andersom


==============


voor allen die mij niet geloven, hier wat code zodat je het kunt repliceren:

*** tabel dump ***
#
# Table structure for table 'personalia'
#

CREATE TABLE personalia (
id int(10) unsigned NOT NULL auto_increment,
name varchar(50) default NULL,
age tinyint(3) unsigned default NULL,
license enum('ja','nee') default 'nee',
PRIMARY KEY (id),
UNIQUE KEY id (id)
) TYPE=MyISAM;


#
# Dumping data for table 'personalia'
#

INSERT INTO personalia VALUES("1", "karel", "19", "nee");
INSERT INTO personalia VALUES("2", "piet", "19", "ja");
INSERT INTO personalia VALUES("3", "jaap", "19", "nee");
INSERT INTO personalia VALUES("4", "hans", "19", "nee");
INSERT INTO personalia VALUES("5", "peter", "20", "ja");
INSERT INTO personalia VALUES("6", "jan", "21", "nee");
INSERT INTO personalia VALUES("7", "gerard", "21", "nee");
*** end of dump ***

query:

MySQL:

SELECT
    age, COUNT(id) AS total
FROM personalia
WHERE license = 'nee'
GROUP BY age
HAVING total >= 2

resultaat:

code:

age total
19  3
21  2

en da's dus niet de bedoeling. age 19 zou niet in de resultset moeten voorkomen.

Ik ben het trouwens met je eens dat die oplossing van _Sunnyboy_ een beetje vies aanvoelt...maarja het werkt wel!

[ Voor 60% gewijzigd door marty op 17-09-2003 16:42 . Reden: ff voorbeeldje toegevoegd ]

[slm]

Mjah... je moet niet snel je records gaan wijzigen he Karel is opeens een jaar jonger

Maar ja, de query van riegstar is juist als je je allereerste vereisten bekijkt:

records waar dezelfde leeftijd meer dan 1x voorkomt
EN waar bij allemaal het rijbewijs op nee staat

Hier voldoet de query prima aan. Dat je werkelijk niemand uit die leeftijdsgroep wil hebben als er ook maar 1 wel een rijbewijs in die _hele_ leeftijdsgroep heeft, was mij niet duidelijk - dat gaf je in een latere post aan. (Ik moet mezelf toch eens aanleren om dit soort posts met toegevoegde info van een TS beter te lezen alhoewel je dit als TS ook in je eerste post zou kunnen editen...)

Nou ja, hoe dan ook, je zult het met die query van Sunnyboy moeten doen. Je zou ook nog zoiets kunnen neerzetten:

SELECT age,count(license) as total,sum(license="nee") as totaal FROM personalia GROUP BY age HAVING totaal>1 and total=totaal

...maar of dat nou zoveel mooier is....

NB. wel een index op (age,license) aanmaken he! anders krijg je temp tables en dat wil je niet.

[Verwijderd]

code:

select  leeftijd,  
sum(if (rijbewijs = 'Nee','0','1')) as rijbewijs_teller, 
count(leeftijd) leeftijd_teller 

from test 

group by  leeftijd 

having rijbewijs_teller = 0 AND 
leeftijd_teller > 1

Werkt dit niet?

edit:

MS-Acces accepteerd geen if, MySQL wel. Dus zelf even testen...

[ Voor 255% gewijzigd door Verwijderd op 17-09-2003 23:37 ]

[_Sunnyboy_]

De oplossing die ik gaf komt uit een MySQL tutorial.

Geen idee hoe je die exacte restricties die jij opgeeft op een andere manier zou willen oplossen in 1 query. Je moet namelijk op een of andere manier voor je GROUP te weten komen of er een rijbewijs in zit, en je wil de goeie total, dus je kan niet met een WHERE al bepaalde records gaan uitsluiten, want die worden dan ook niet in je total opgenomen.

Geen idee hoe de performance is bij een SUM over een IF, maar ik neem aan dat jij dit niet op een tabel met 100.000 records gaat toepassen, dus we zullen hier wel over 0.005 seconden praten ofzo

Psst Backlash

_Sunnyboy_ schreef op 17 September 2003 @ 11:54:
Werkt dit niet?

code:

SELECT 
leeftijd, 
COUNT(leeftijd) AS total,
SUM(IF(rijbewijs='ja',1,0)) AS iemand_rijbewijs 
FROM table
GROUP BY leeftijd 
HAVING total >= 2 AND iemand_rijbewijs=0
ORDER BY total DESC

Is in feite hetzelfde als wat jij voorsteld. Maar fijn dat je het met me eens bent

[ Voor 34% gewijzigd door _Sunnyboy_ op 18-09-2003 09:11 ]

[_Sunnyboy_]

dubbelpost

[ Voor 97% gewijzigd door _Sunnyboy_ op 18-09-2003 09:10 ]

[marty]

slm schreef op 17 september 2003 @ 20:38:
Maar ja, de query van riegstar is juist als je je allereerste vereisten bekijkt:

[...]

Hier voldoet de query prima aan. Dat je werkelijk niemand uit die leeftijdsgroep wil hebben als er ook maar 1 wel een rijbewijs in die _hele_ leeftijdsgroep heeft, was mij niet duidelijk - dat gaf je in een latere post aan.

Niet dat ik behoefte heb aan een welles nietes spelletje, maar ik gaf het in het begin al aan hoor: (dat het jou niet duidelijk was is een 2e )

=> nu wil ik een query doen die alleen rows teruggeeft van:
- records waar dezelfde leeftijd meer dan 1x voorkomt
- EN waar bij allemaal het rijbewijs op nee staat
(leeftijd 19 dus in dit geval)

geef nota bene nog een keer extra aan dat alleen leeftijdsgroep 19 in aanmerking komt...

SELECT age,count(license) as total,sum(license="nee") as totaal FROM personalia GROUP BY age HAVING totaal>1 and total=totaal

...maar of dat nou zoveel mooier is....

Ik zou allicht eens kunnen kijken of het 1 sneller is dan het ander....