[MySQL] Count met uitkomst 0

[Deddiekoel]

Ik ben hier nu al een tijdje zoet mee maar het wil niet lukken.
Ik heb een master en child tabel. En de child tabel bevat een status veld. Nu wil ik een lijst van alle records uit de master tabel met een count van alle child records van een bepaalde status ook al is die count gelijk aan nul.

Dit is wat ik heb maar hier verschijnen de master records met count 0 niet in:

SQL:

SELECT m.master_id, m.masternumber, m.mastersequence, count( * ) AS available
FROM master m, child c
WHERE m.master_id = c.master_id
AND c.status = 'Vrij'
AND m.uber_id =1234
GROUP BY m.master_id
ORDER BY m.masternumber

Ik ben ook al met een outer join in de weer geweest maar ook dacht mocht niet baten. HAVING werkte ook niet omdat status dan in de SELECT moet zitten.
Het is MySQL 5.0.37 btw.

[ Voor 12% gewijzigd door Deddiekoel op 13-09-2007 12:08 ]

[__fred__]

Subquery van je count maken of een left join gebruiken.

[Creepy]

En je vraag is nu precies wat en hoe denk je dat wij dat voor je zouden kunnen oplossen?

Is de count altijd 0 maar krijg je verder wel een aantal records of krijg je helemaal geen records? Krijg je records terug als je de count en group by weg laat? Heb je de FAQ al eens bekeken m.b.t. group by? Tip: alleen MySQL staat toe dat je velden niet in je group by op hoeft te nemen die wel in je select staan. Ondanks dat de devvers van MySQL het een feature vinden vinden een hoop andere mensen dat het een bug is.

[ Voor 6% gewijzigd door Creepy op 13-09-2007 12:11 ]

[Deddiekoel]

Creepy schreef op donderdag 13 september 2007 @ 12:08:
En je vraag is nu precies wat en hoe denk je dat wij dat voor je zouden kunnen oplossen?

Is de count altijd 0 maar krijg je verder wel een aantal records of krijg je helemaal geen records? Heb je de FAQ al eens bekeken m.b.t. group by? Tip: alleen MySQL staat toe dat je velden niet in je group by op hoeft te nemen die wel in je select staan. Ondanks dat de devvers van MySQL het een feature vinden vinden een hoop andere mensen dat het een bug is.

Mijn vraag is hoe ik de master records met een child count van 0 toch in mijn resultset kan krijgen.
Ik heb de FAQ gelezen maar kom er niet uit.

Ik heb ook een LEFT OUTER JOIN geprobeerd maar ook dat mocht niet baten.

[ Voor 87% gewijzigd door Deddiekoel op 13-09-2007 12:12 ]

[Voutloos]

Dat kan wel, maar je staart je blind op het idee dat je rijen met 0 childs niet terug krijgt. Er is namelijk een 2e probleem: je hebt c.status = 'Vrij' in je where clause.
Pas als je ook een c.status van null toestaat heb je iets aan je outer join.

Overigens: imo is het gebruiken van ',' als join operator slechte sql stijl. Schrijf altijd het join type voluit, en maak gebruik van using/on ipv een where clause om de relatie te leggen.

[Bolukan]

Niet mooi misschien, maar werkt wel:

SQL:

SELECT
 m.id, 
 Sum(IIf([status] Is Null,0,IIf([status]='Vrij',1,0))) AS Aantal
FROM m 
 LEFT JOIN k ON m.id = k.id
GROUP BY
 m.id;

Edit: Dit voorbeeld komt uit Access, maar is wel om te zetten in MySql

[ Voor 19% gewijzigd door Bolukan op 13-09-2007 13:17 ]

[P.O. Box]

echt makkelijker dan met een LEFT JOIN kan het niet hoor.... wat heb je geprobeerd met een join dat niet werkt?

edit: en zonder zo'n IF gedoe moet dat ook prima kunnen...

eventuele hint: de ON clause van de join hoeft niet perse maar 1 voorwaarde te bevatten...

[ Voor 41% gewijzigd door P.O. Box op 13-09-2007 13:21 ]

[kenneth]

Bolukan schreef op donderdag 13 september 2007 @ 13:01:
Niet mooi misschien, maar werkt wel:

Kent MySQL IIf dan

[user109731]

Voor MySQL kun je IF() gebruiken

[kenneth]

Ik vond al zo weinig

[Deddiekoel]

Dit is de JOIN die ik heb gebruikt:

SQL:

SELECT m.master_id, m.masternumber, m.mastersequence, count( * ) AS available
FROM master m LEFT OUTER JOIN child c ON m.master_id = c.master_id
WHERE c.status = 'Vrij'
AND m.uber_id =1234
GROUP BY m.master_id
ORDER BY m.masternumber

Maar ik heb ook het idee dat de status='Vrij' problemen geeft. Ik wil dus een overzicht van alle master records met een count van alle childs met status is Vrij ook als dit er 0 zijn.

Ik ben ook met een subselect aan het stoeien gegaan met dit als resultaat:

SQL:

SELECT m.master_id, m.masternumber, m.mastersequence, count( * ) AS available
FROM master m LEFT OUTER JOIN (
SELECT master_id, status, count(*) 
FROM child 
GROUP BY master_id, status) c ON m.master_id = c.master_id
WHERE c.status = 'Vrij'
AND m.uber_id =1234
GROUP BY m.master_id
ORDER BY m.masternumber

Deze geeft wel alle master records maar de correct count van de childs met status Vrij...

IF() gebruiken is volgens mij geen optie omdat status altijd een waarde heeft.

[ Voor 3% gewijzigd door Deddiekoel op 13-09-2007 14:02 ]

[Janoz]

Tja, waar je constant de mist mee in gaat is dat je blijkbaar neit helemaal duidelijk hebt hoe aggregerende functies werken. Het enige dat count doet is het tellen van de records in de result set. Door een group by toe te voegen kan dat gegroepeerd worden zodat je meerdere totalen krijgt. Hoe wil je uberhaupt een resultaat terugkrijgen wat niet in je resultset staat?

De oplossing is om eerst een select te doen waarbij je de children telt en dit groepeerd op master_id. Dit join je vervolgens met de rest zodat er een null komt te staan bij elke master die geen children heeft. Eventueel kun je dit dan omzetten naar een 0 met de if.

[Deddiekoel]

Janoz schreef op donderdag 13 september 2007 @ 14:10:
Tja, waar je constant de mist mee in gaat is dat je blijkbaar neit helemaal duidelijk hebt hoe aggregerende functies werken. Het enige dat count doet is het tellen van de records in de result set. Door een group by toe te voegen kan dat gegroepeerd worden zodat je meerdere totalen krijgt. Hoe wil je uberhaupt een resultaat terugkrijgen wat niet in je resultset staat?

De oplossing is om eerst een select te doen waarbij je de children telt en dit groepeerd op master_id. Dit join je vervolgens met de rest zodat er een null komt te staan bij elke master die geen children heeft. Eventueel kun je dit dan omzetten naar een 0 met de if.

Dit probeerde ik te bereiken met mijn subselect maar blijkaar gaat dat nog niet goed.

[Janoz]

Waarom staat er in die omsluitende query dan nog steeds een count?

[Bolukan]

SQL:

SELECT
 m.master_id,
 m.masternumber,
 m.mastersequence, 
 Sum(If(c.[Status] Is Null,0,If(c.[Status]='Vrij',1,0))) AS Available 
FROM
 master m 
 LEFT JOIN child c ON m.master_id = c.master_id 
WHERE
 m.uber_id =1234
GROUP BY
 m.master_id,
 m.masternumber,
 m.mastersequence
ORDER BY
 m.masternumber

[Deddiekoel]

Ik heb hem denk ik:

SQL:

SELECT m.master_id, m.masternumber, m.mastersequence, vailable
FROM master m LEFT OUTER JOIN (
SELECT master_id, status, count( * ) AS available
FROM child 
WHERE status = 'Vrij'
GROUP BY master_id, status) c ON m.master_id = c.master_id
WHERE m.uber_id =1234
GROUP BY m.master_id
ORDER BY m.masternumber

Hiermee krijg ik alle masters terug en en bij de lege master "NULL" voor aantal. Hier moet dus nog een IF bij om het af te maken.

[Deddiekoel]

De oplossing voor mij probleem is:

SQL:

SELECT m.master_id, m.masternumber, m.mastersequence, IF(available IS NULL, 0, available)
FROM master m LEFT OUTER JOIN (
SELECT master_id, status, count(*) as available
FROM child 
WHERE status = 'Vrij'
GROUP BY master_id, status) c ON m.master_id = c.master_id
WHERE m.uber_id =1234
GROUP BY m.master_id
ORDER BY m.masternumber

[P.O. Box]

ahum... wat dacht je van:

SQL:

SELECT m.master_id, m.masternumber, m.mastersequence, count( * ) AS available
FROM master m
LEFT JOIN child c
ON
m.master_id = c.master_id AND c.status = 'Vrij'
WHERE
m.user_id =1234
GROUP BY m.master_id, m.masternumber, m.mastersequence
ORDER BY m.masternumber

[Deddiekoel]

Edwardvb schreef op donderdag 13 september 2007 @ 16:54:
ahum... wat dacht je van:

SQL:

SELECT m.master_id, m.masternumber, m.mastersequence, count( * ) AS available
FROM master m
LEFT JOIN child c
ON
m.master_id = c.master_id AND c.status = 'Vrij'
WHERE
m.user_id =1234
GROUP BY m.master_id, m.masternumber, m.mastersequence
ORDER BY m.masternumber

Deze werkt op zich wel maar geeft ipv 0 een 1 terug waar de child count 0 is...

[P.O. Box]

Deddiekoel schreef op donderdag 13 september 2007 @ 17:05:
[...]

Deze werkt op zich wel maar geeft ipv 0 een 1 terug waar de child count 0 is...

oh ja? waarom dan? dat snap ik niet...

[ACM]

Edwardvb schreef op donderdag 13 september 2007 @ 17:06:
oh ja? waarom dan? dat snap ik niet...

Omdat je * telt en er altijd minstens een record is. Wat je moet tellen is count(c.*) of in mysql-compatibiliteit iets als count(c.id) of count(c.master_id), oftewel het aantal niet-null zijnde records van c.

Overigens is de reden waarom Edwardvb's query wel werkt tov het eerste left-join voorbeeld van Deddiekoel, omdat bij Deddiekoel er checks op tabel c in de WHERE werden gestopt, waardoor de c in dit geval geforceerd niet null mocht zijn... en er dus effectief gewoon een inner join was opgeschreven ipv een left join.

[ Voor 30% gewijzigd door ACM op 13-09-2007 17:20 ]

[Voutloos]

Tja, dat had ik al lang en breed gezegd.
Dat vervolgens die count(*) niet goed zou zijn had ik ook al gezien, maar ik hoopte dat iedereen adhv de count() documentatie daar wel uit zou komen.

[P.O. Box]

ACM schreef op donderdag 13 september 2007 @ 17:19:
[...]

Omdat je * telt en er altijd minstens een record is. Wat je moet tellen is count(c.*) of in mysql-compatibiliteit iets als count(c.id) of count(c.master_id), oftewel het aantal niet-null zijnde records van c.

oh ja tuurlijk... ... ff niet bij nagedacht....

nou ja, iig vind ik "mijn" oplossing beter dan die gekke if-constructie...

[Deddiekoel]

Ik heb het even geprobeerd en idd met een count puur op de child table (maakt niet uit welk veld, maar c.* slikt MySQL niet) wordt de waarde 0!

Mooiste oplossing imo daardoor:

SQL:

SELECT m.master_id, m.masternumber, m.mastersequence, count( c.master_id ) AS available
FROM master m
LEFT JOIN child c
ON
m.master_id = c.master_id AND c.status = 'Vrij'
WHERE
m.user_id =1234
GROUP BY m.master_id, m.masternumber, m.mastersequence
ORDER BY m.masternumber

De "truc" zit hem er dus in dat je met meerdere velden je join uitvoert.
Is er nog verschil tussen LEFT JOIN en LEFT OUTER JOIN? Resultset is nl. bij beide hetzelfde.

[ Voor 15% gewijzigd door Deddiekoel op 14-09-2007 09:59 ]

[Voutloos]

Nee en je hebt nu een prima query.

[Deddiekoel]

Voutloos schreef op vrijdag 14 september 2007 @ 10:33:
Nee en je hebt nu een prima query.

Idd, bedankt aan iedereen voor de hulp.