[MySql] dubbele count uit 1 tabel met Where clause

[Verwijderd]

Dames, Heren,

Momenteel probeer ik uit 1 tabel 2 counts te halen. Ik ben geen ster in dit soort dingen dus heb eerst tweakers zitten doorspitten. De dingen die ik heb gevonden komen neer op inner joins met andere tabels, en dit is echter met maar 1 tabel.

Ik heb momenteel de volgende MySql query gebruikt

SQL:

SELECT COUNT(opdrachtgevers_v2.id) AS `active` FROM opdrachtgevers_v2 WHERE active='1';

Echter is dit voor maar 1 veld.

Doel:
-Optellen van het aantal records met een WHERE `active`='1', als `active`;
-Optellen van het aantal records met een WHERE `active`='0', als `non-active`;

Hierna kan ik met php simpel de output printen ($result['active/non-active'];).
Ik zat zelf te denken aan een subquery maar ik weet niet of dit wel 'de juiste manier' is??

Ik hoop dat iemand mij zou willen helpen hiermee? Bij voorbaat dank!

[Mark Lor]

Hiervoor gebruik je group by

Iets als

SQL:

SELECT opdrachtgevers_v2.id, COUNT(opdrachtgevers_v2.id) FROM opdrachtgevers_v2 GROUP BY opdrachtgevers_v2.id

[ Voor 3% gewijzigd door Mark Lor op 28-03-2010 16:27 ]

[CodeCaster]

Mark Lor schreef op zondag 28 maart 2010 @ 16:26:
Hiervoor gebruik je group by

Iets als

SQL:

SELECT opdrachtgevers_v2.id, COUNT(opdrachtgevers_v2.id) AS `active` FROM opdrachtgevers_v2 GROUP BY opdrachtgevers_v2.id

Dat is niet wat hij bedoelt.

@TS: wat heb je zelf al gevonden?

Eerste hit bij [google=mysql multiple count in single query]:

SQL:

SELECT
  X.column1,
  X.column2,
  X.column3,
  X.column4
FROM (
  SELECT 
     column1, column2,
     (select count(*) from table2_ where table2_id = table1.column1) as column3,
     (select count(*) from table3_ where table3_id = table1.column2) as column4,
 FROM table1
) AS X 
WHERE X.column3 > X.column4

[Verwijderd]

Beide bedankt voor je response!

CodeCaster schreef op zondag 28 maart 2010 @ 16:30:
[...]

Dat is niet wat hij bedoelt.

@TS: wat heb je zelf al gevonden?

Eerste hit bij [google=mysql multiple count in single query]:

SQL:

SELECT
  X.column1,
  X.column2,
  X.column3,
  X.column4
FROM (
  SELECT 
     column1, column2,
     (select count(*) from table2_ where table2_id = table1.column1) as column3,
     (select count(*) from table3_ where table3_id = table1.column2) as column4,
 FROM table1
) AS X 
WHERE X.column3 > X.column4

Wat ik heb gevonden: http://dev.mysql.com/tech...rticles/wizard/page3.html
echter begreep ik hier vrij weinig van....

Ik ga jou voorstel proberen!

@Mark Lor: Het is niet wat ik zoek. Wanneer ik je query aanpas met GROUP BY op ACTIVE ipv ID dan krijg ik wel de gewenste resultaten! alleen wil ik het als ACTIVE en NON-ACTIVE terug krijgen. Thanks anyway!

[Tharulerz]

Dat is misschien wel wat hij bedoelt, maar waarom dingen ingewikkeld gaan maken via subqueries en dergelijke, terwijl hij perfect gewoon met een GROUP BY kan werken?

[CodeCaster]

Tharulerz schreef op zondag 28 maart 2010 @ 16:35:
Dat is misschien wel wat hij bedoelt, maar waarom dingen ingewikkeld gaan maken via subqueries en dergelijke, terwijl hij perfect gewoon met een GROUP BY kan werken?

Hoe kun je met een group by meerdere where's gebruiken?

[P_de_B]

SELECT Active, COUNT(*) FROM Tabel
GROUP BY Active

Dit levert een resultset als:

1 2304
0 3534

In je client 1 vertalen naar active en 0 naar niet active

[Verwijderd]

Momenteel heb ik een combinatie gemaakt van beide suggesties:

SQL:

SELECT 
    info.`active`,
    info.`non-active`
FROM (  SELECT 
        (SELECT COUNT(*) FROM opdrachtgevers_v2 WHERE active='0') AS `non-active`,
        (SELECT COUNT(*) FROM opdrachtgevers_v2 WHERE active='1') AS `active`
    FROM 
        opdrachtgevers_v2)
AS  `info` GROUP BY `active`, `non-active`;

Het geeft me wat ik wil zien, alleen is nu de vraag: is het ook de juiste manier?? (ben geen held op dit gebied...)

[P_de_B]

Nee, dat is niet de juiste manier. Je doet nu twee subqueries die niet nodig zijn. Ook is de group by niet nodig. Dat gebruik je alleen als je in de SELECT een aggregate functie gebruikt

[Verwijderd]

P_de_B schreef op zondag 28 maart 2010 @ 16:44:
Nee, dat is niet de juiste manier. Je doet nu twee subqueries die niet nodig zijn. Ook is de group by niet nodig. Dat gebruik je alleen als je in de SELECT een aggregate functie gebruikt

Als ik de group by weg haal dan krijg ik 7 records terug in 2 columns met allemaal dezelfde waarde. Daarom dat ik die erin heb geplaatst.

Ik ga nog effe verder sleutelen dan. Bedankt voor je uitleg

[P_de_B]

Mijn suggestie werkt toch goed?

SQL:

SELECT Active, COUNT(*) FROM Tabel
GROUP BY Active

[Verwijderd]

P_de_B schreef op zondag 28 maart 2010 @ 16:53:
Mijn suggestie werkt toch goed?

SQL:

SELECT Active, COUNT(*) FROM Tabel
GROUP BY Active

Ja en nee.

Jou query:

SQL:

SELECT Active, COUNT(*) FROM opdrachtgevers_v2 GROUP BY Active;

geeft:


Gecombineerde 'bastard'-query van mij:

SQL:

SELECT 
    info.`active`,
    info.`non-active`
FROM (  SELECT 
        (SELECT COUNT(*) FROM opdrachtgevers_v2 WHERE active='0') AS `non-active`,
        (SELECT COUNT(*) FROM opdrachtgevers_v2 WHERE active='1') AS `active`
    FROM opdrachtgevers_v2)
AS  `info` GROUP BY `active`, `non-active`;

geeft:



Afbeelding 2 geeft mijn gewenste resultaat maar is, zoals aangegeven, niet de juiste methode. Ik ben dus nu op zoek naar een acceptabel alternatief voor hetzelfde resultaat.

Snap je wat ik bedoel ?

[P_de_B]

Ik snap wat je bedoelt, maar je kunt dit toch eenvoudig in je clientapplicatie oplossen? Maakt het zo veel uit dat het twee records zijn in plaats van twee kolommen?

Als het écht moet zou ik zoiets doen:

SQL:

SELECT SUM(CASE Active WHEN 1 THEN 1 ELSE 0 END) as Active, SUM(CASE Active WHEN 0 THEN 1 ELSE 0 END) as non-active
FROM Tabel

[ Voor 1% gewijzigd door P_de_B op 28-03-2010 17:19 . Reden: sum ipv count ]

[Bjornski]

De oplossing die P_de_B geeft is veruit de netste en bovendien de snelste.

Als het persé in één rij moet, doe dan dit:

SQL:

SELECT
  (SELECT COUNT(*) FROM tabel WHERE active = 0) as Inactive,
  (SELECT COUNT(*) FROM tabel WHERE active = 1) as Active
FROM DUAL;

Ik weet niet helemaal zeker of dit klopt (niet getest), maar iets in deze trant zou voldoende moeten zijn.

Edit: Oplossing van P_de_B in de post hierboven is mooier dan de mijne.

[ Voor 8% gewijzigd door Bjornski op 28-03-2010 17:19 ]

[Verwijderd]

P_de_B schreef op zondag 28 maart 2010 @ 17:15:
Ik snap wat je bedoelt, maar je kunt dit toch eenvoudig in je clientapplicatie oplossen? Maakt het zo veel uit dat het twee records zijn in plaats van twee kolommen?

Als het écht moet zou ik zoiets doen:

SQL:

SELECT SUM(CASE Active WHEN 1 THEN 1 ELSE 0 END) as Active, SUM(CASE Active WHEN 0 THEN 1 ELSE 0 END) as non-active
FROM Tabel

Je hebt gelijk wat betreft de kolommen maar weet niet hoe (en heb het nog niet geprobeerd) hoe ik in PHP kan zeggen dat ik, met jou query, kan zeggen waar ik welk getal wil printen (print($result['count(*)']); zal dan toch 2 waardes bevatten??).

Overigens, ik heb je query toegepast maar dat geeft verkeerde waardes terug:

SQL:

SELECT 
    COUNT(CASE `active` WHEN 1 THEN 1 ELSE 0 END) AS `active`, 
    COUNT(CASE `active` WHEN 0 THEN 1 ELSE 0 END) AS `non-active`
FROM    `opdrachtgevers_v2`;

Resulteerd in 2 columns met de waarde 7.

Ik ga even zoeken naar de hoe die CASE werkt in mysql.

Vindt het wel heel fijn dat je me hiermee helpt, heb nu al weer nieuwe dingen geleerd!

[P_de_B]

Ja, dat was een foutje van mij, ik had al een edit gedaan. Je moet uiteraard SUM ipv COUNT doen, zie mijn edit

[DamadmOO]

P_de_B schreef op zondag 28 maart 2010 @ 17:15:
Ik snap wat je bedoelt, maar je kunt dit toch eenvoudig in je clientapplicatie oplossen? Maakt het zo veel uit dat het twee records zijn in plaats van twee kolommen?

Als het écht moet zou ik zoiets doen:

SQL:

SELECT COUNT(CASE Active WHEN 1 THEN 1 ELSE 0 END) as Active, COUNT(CASE Active WHEN 0 THEN 1 ELSE 0 END) as non-active
FROM Tabel

SUM voor dit soort dingen is alsnog vrij lelijk.

SQL:

SELECT SUM(CASE Active WHEN 1 THEN Active ELSE NULL END) as Active, SUM(CASE Active WHEN 0 THEN Active ELSE NULL END) as non-active
FROM Tabel

Dit is wat ik zou gebruiken als het echt in 1 regel moet, maar de TS is dus bezig om in SQL rekening te houden met hoe je de data output op het scherm. En dat is iets waar je SQL dus gewoon niet voor moet gebruiken. De eerdere gegeven oplossen

P_de_B schreef op zondag 28 maart 2010 @ 16:53:

SQL:

SELECT Active, COUNT(*) FROM Tabel
GROUP BY Active

is degene die gewoon gebruikt zou moeten worden.

[UltimateB]

Waarom niet gewoon het iets anders

SQL:

SELECT (count(1) - sum(active)) as inactive,
       sum(active) as active
FROM   opdrachtgevers_v2

Beetje pseudo maar lijkt mij een snelle oplossing. Wel handig om dan te zorgen voor de juiste datatypes e.d.

Verwijderd schreef op zondag 28 maart 2010 @ 17:30:
[...]

Je bent geniaal!

SQL:

SELECT 
    SUM(CASE `active` WHEN 1 THEN 1 ELSE 0 END) AS `active`, 
    SUM(CASE `active` WHEN 0 THEN 1 ELSE 0 END) AS `non-active`
FROM    `opdrachtgevers_v2`;

Geeft het gewenste resultaat terug!
Ik ben beniewd naar het verschil tussen SUM en COUNT, eens googlen maar!

Ieder bedankt voor de hulp! Special thanks @P_de_b!

Die case voor active is een beetje loos, als het 1 is dan 1 anders 0, terwijl de waarde al 1 is.

[ Voor 62% gewijzigd door UltimateB op 28-03-2010 17:32 ]

[Verwijderd]

P_de_B schreef op zondag 28 maart 2010 @ 17:26:
Ja, dat was een foutje van mij, ik had al een edit gedaan. Je moet uiteraard SUM ipv COUNT doen, zie mijn edit

Je bent geniaal!

SQL:

SELECT 
    SUM(CASE `active` WHEN 1 THEN 1 ELSE 0 END) AS `active`, 
    SUM(CASE `active` WHEN 0 THEN 1 ELSE 0 END) AS `non-active`
FROM    `opdrachtgevers_v2`;

Geeft het gewenste resultaat terug!
Ik ben beniewd naar het verschil tussen SUM en COUNT, eens googlen maar!

Ieder bedankt voor de hulp! Special thanks @P_de_b!

[P_de_B]

COUNT telt het aantal rijen in de resultset, SUM telt op. Het eerste CASE statement geeft een 1 voor alle records waar active=true. Door deze op te tellen weet je hoeveel records hieraan voldoen.

Toch nog even, waarom wil je het nu in twee kolommen in plaats van twee rijen?

[Tharulerz]

Verwijderd schreef op zondag 28 maart 2010 @ 17:25:
[...]


Je hebt gelijk wat betreft de kolommen maar weet niet hoe (en heb het nog niet geprobeerd) hoe ik in PHP kan zeggen dat ik, met jou query, kan zeggen waar ik welk getal wil printen (print($result['count(*)']); zal dan toch 2 waardes bevatten??).

Misschien kan je je best eens verdiepen in PHP, MySQL en Arrays.... Je laat nu uitschijnen dat je maar 1 rij kan uitprinten uit een result...

[glashio]

Verwijderd schreef op zondag 28 maart 2010 @ 16:22:
Echter is dit voor maar 1 veld.

Doel:
-Optellen van het aantal records met een WHERE `active`='1', als `active`;
-Optellen van het aantal records met een WHERE `active`='0', als `non-active`;

SQL:

SELECT
    SUM(CASE WHEN active='1' THEN 1 ELSE 0 END) AS cnt_active,
    SUM(CASE WHEN active='1' THEN 0 ELSE 1 END) AS cnt_inactive
FROM
    opdrachtgevers_v2

Logisch, en leesbaar

Edit: GROUP BY is idd niet nodig...

[ Voor 6% gewijzigd door glashio op 28-03-2010 17:42 . Reden: GROUP BY verwijdert ]

[pedorus]

Bjornski schreef op zondag 28 maart 2010 @ 17:19:

SQL:

SELECT
  (SELECT COUNT(*) FROM tabel WHERE active = 0) as Inactive,
  (SELECT COUNT(*) FROM tabel WHERE active = 1) as Active
FROM DUAL;

Ik weet niet helemaal zeker of dit klopt (niet getest), maar iets in deze trant zou voldoende moeten zijn.

Laat regel 4 weg, zet een ';' aan het einde van regel 3, en hij lijkt me prima. Hoewel je eigenlijk gewoon 2 rijen eruit zou moeten laten komen, en het verdere probleem in de UI moet oplossen (dus P_de_B in "[MySql] dubbele count uit 1 tabel met Wh...").

Verwijderd schreef op zondag 28 maart 2010 @ 17:30:

SQL:

SELECT 
    SUM(CASE `active` WHEN 1 THEN 1 ELSE 0 END) AS `active`, 
    SUM(CASE `active` WHEN 0 THEN 1 ELSE 0 END) AS `non-active`
FROM    `opdrachtgevers_v2`;

Geeft het gewenste resultaat terug!

Met naar ik gok een beroerde performance, omdat sleutels niet gebruikt kunnen worden (kun je zien met explain)..

[Verwijderd]

Tharulerz schreef op zondag 28 maart 2010 @ 17:34:
[...]


Misschien kan je je best eens verdiepen in PHP, MySQL en Arrays.... Je laat nu uitschijnen dat je maar 1 rij kan uitprinten uit een result...

Ik weet wel hoe het zit met multidimensionale array's, maar waarom zou ik dat oplossen in mijn clientapplicatie als het al in het begin gewoon goed kan worden doorgegeven?

Momenteel werkt de query van P_de_B. Aangegeven is dat die van hem beter is dan de mijne en qua snelheid is dat ook overduidelijk zo.

Het maakt mij verder niet uit, ik wil me verder verdiepen in deze wereld en daarom dat ik graag jullie responses wil horen.

[P_de_B]

pedorus schreef op zondag 28 maart 2010 @ 17:35:

Met naar ik gok een beroerde performance, omdat sleutels niet gebruikt kunnen worden (kun je zien met explain)..

Ook bij de variant met GROUP BY zullen denk ik geen indexen gebruikt worden. De mogelijke waarden (1 en 0 en misschien NULL) zijn niet selectief genoeg om een index te gebruiken. Ik vermoed dat je sowieso een tablescan krijgt.