[SQL] Meerdere queries samenvoegen met (*counts)

[Verwijderd]

Ik zit met een probleempje. Ben niet pro SQL dus vandaar dat ik dit ef post

Ik wil een querie maken waarmee ik de resultaten vanuit een querie optel in meerdere kolommen. Klink vaag ik weet het. Ik werk met de volgende queries:


HOOFDQUERIE: (Selecteerd openstaande facturen (tickets))

qry_OpenstaandeTickets2:
SELECT HDTicket.*
FROM HDTicket
WHERE (((HDTicket.Afgedaan) Is Null)) AND ((HDTicket.Stat1) <> "klaar");


BIJ QUERIE 1: (Selecteerd de medewerker (specialist) en zijn/haar openstaande facturen)

Aantal openstaande tickets per speci:
SELECT qry_OpenstaandeTickets_2.spec1 AS Specialist, Count(qry_OpenstaandeTickets_2.[W/S]) AS [Openstaande Tickets]
FROM qry_OpenstaandeTickets_2
GROUP BY qry_OpenstaandeTickets_2.spec1;


BIJ QUERIE 2,3,4: (Selecteerd de medewerker (specialist) en zijn/haar openstaande facturen: installaties, storingen en wijzigingen (zijn ticketsoorten))

Aantal openstaande installaties per speci:
SELECT qry_OpenstaandeTickets_2.spec1 AS Specialist, Count(qry_OpenstaandeTickets_2.[W/S]) AS [Openstaande Installaties]
FROM qry_OpenstaandeTickets_2
WHERE (((qry_OpenstaandeTickets_2.[W/S])="Installatie"))
GROUP BY qry_OpenstaandeTickets_2.spec1;

Aantal openstaande storingen per speci:
SELECT qry_OpenstaandeTickets_2.spec1 AS Specialist, Count(qry_OpenstaandeTickets_2.[W/S]) AS [Openstaande Storingen]
FROM qry_OpenstaandeTickets_2
WHERE (((qry_OpenstaandeTickets_2.[W/S])="Storing"))
GROUP BY qry_OpenstaandeTickets_2.spec1;

Aantal openstaande wijzigingen per speci:
SELECT qry_OpenstaandeTickets_2.spec1 AS Specialist, Count(qry_OpenstaandeTickets_2.[W/S]) AS [Openstaande Wijzigingen]
FROM qry_OpenstaandeTickets_2
WHERE (((qry_OpenstaandeTickets_2.[W/S])="Wijziging"))
GROUP BY qry_OpenstaandeTickets_2.spec1;


Wat ik dus wil is dat er een tabel gegenereerd wordt wat het volgende resultaat geeft:

kolom1: de specialist
kolom2: aantal openstaande tickets per specialist
kolom3: aantal openstaande tickets waarvan aantal installaties
kolom4: aantal openstaande tickets waarvan aantal storingen
kolom5: aantal openstaande tickets waarvan aantal wijzigingen

Het zijn nu 4 losse queries zegmaar maar die wil op op zo'n manier samenvoegen dat ze in 1 querie kunnen worden verwerkt. Wat belangrijk is is dat ookal bestaan er geen openstaande tickets voor een persoon, de naam toch moet worden weergegeven en er een waarde 0 achter zijn/haar naam moet komen. Dit geld voor aantal openstaande tickets en voor openstaande tickets waarvan installaties, storingen en wijzigingen. alles dus ;p

HELPHELP

[Gert]

Je zou eens naar inner joins kunnen kijken om in ieder geval ook null resultaten te krijgen.

Verder kan je het vrij simpel met een hele batterij if statements ala sum(iif(type="storing", 1, 0)) het oplossen maar of dat netjes is weet ik niet.

[ Voor 139% gewijzigd door Gert op 25-11-2004 10:44 ]

[Verwijderd]

Gert schreef op donderdag 25 november 2004 @ 10:39:
Je zou eens naar inner joins kunnen kijken om in ieder geval ook null resultaten te krijgen.

Verder kan je het vrij simpel met een hele batterij if statements ala sum(iif(type="storing", 1, 0)) het oplossen maar of dat netjes is weet ik niet.

geen id hoe dat precies zou moeten dan :"(
zou je een begin voorbeeldje kunnen maken?

[jvdmeer]

Even een kort vraagje: Welke DBMS gebruik je?
Want onderstaand werkt waarschijnlijk alleen onder MS-SQL.

Even ongetest:

SQL:

SELECT x1.spec1 AS Specialist, x1.Open, x2.Install, x3.Storing, x4.Wijziging
  FROM 
  (
    SELECT qry_OpenstaandeTickets_2.spec1, Count(qry_OpenstaandeTickets_2.[W/S]) AS [Open]
      FROM qry_OpenstaandeTickets_2
      GROUP BY qry_OpenstaandeTickets_2.spec
  ) x1
  LEFT JOIN
  (
    SELECT qry_OpenstaandeTickets_2.spec1, Count(qry_OpenstaandeTickets_2.[W/S]) AS [Install]
      FROM qry_OpenstaandeTickets_2
      WHERE (((qry_OpenstaandeTickets_2.[W/S])="Installatie"))
      GROUP BY qry_OpenstaandeTickets_2.spec1
  ) x2 ON x1.Spec1=x2.Spec1
  LEFT JOIN
  (
    SELECT qry_OpenstaandeTickets_2.spec1, Count(qry_OpenstaandeTickets_2.[W/S]) AS [Storing]
      FROM qry_OpenstaandeTickets_2
      WHERE (((qry_OpenstaandeTickets_2.[W/S])="Storing"))
      GROUP BY qry_OpenstaandeTickets_2.spec1
  ) x3 ON x1.Spec1=x3.Spec1
  LEFT JOIN
  (
    SELECT qry_OpenstaandeTickets_2.spec1, Count(qry_OpenstaandeTickets_2.[W/S]) AS [Wijziging]
      FROM qry_OpenstaandeTickets_2
      WHERE (((qry_OpenstaandeTickets_2.[W/S])="Wijziging"))
      GROUP BY qry_OpenstaandeTickets_2.spec1;
  ) x4 ON x1.Spec1=x4.Spec1

[ Voor 7% gewijzigd door jvdmeer op 25-11-2004 10:52 . Reden: Vraag over DBMS ]

[judgem]

W3schools to the resque: http://www.w3schools.com/sql/sql_join.asp

[Verwijderd]

jvdmeer schreef op donderdag 25 november 2004 @ 10:51:
Even een kort vraagje: Welke DBMS gebruik je?
Want onderstaand werkt waarschijnlijk alleen onder MS-SQL.

Even ongetest:

SQL:

WOW, erg gaaf dat je dit voor me hebt kunnen maken. Ik heb alleen nog wel een foutje:

Syntaxfout (operator ontbreekt) in querie-expressie x1.spec=x2.spec1
(
SELECT qry_OpenstaandeTickets_2.spec1, Count(qry_OpenstaandeTickets_2.[W/S]) AS [Storing]
FROM qry_OpenstaandeTickets_2
WHERE (((qry_OpenstaandeTickets_2.[W/S])="Storing"))
GROUP BY qry_Opensta.

Ik gebruik gewoon SQL via MS Access dat dat zit wel goed volgens mij

[judgem]

@TS:

Heb je al eerder met SQL gewerkt? Zo nee dan raad ik je aan je wat meer te belezen in het onderwerp want een goede basiskennis van SQL is onontbeerlijk en kan je veel voordelen bieden.

Of zit er veel haast achter deze klus die je nu doet?

[Verwijderd]

judgem schreef op donderdag 25 november 2004 @ 11:08:
@TS:

Heb je al eerder met SQL gewerkt? Zo nee dan raad ik je aan je wat meer te belezen in het onderwerp want een goede basiskennis van SQL is onontbeerlijk en kan je veel voordelen bieden.

Of zit er veel haast achter deze klus die je nu doet?

Ik heb wel een basis kennis dan sql maar het is alweer een tijdje geleden dat ik deze kennis heb opgedaan dus het is weer effetjes wennen enzo. De querie waar ik het nu over heb zit opzich wel haast achter omdat ik em morgen moet laten zien aan mn opdrachtgever (stage). In principe had ik het al maar dan allemaal losstaande queries. Toen kwam ik er echter achter dat als ik een rapport wou maken ik geen 0 velden kreeg waardoor de cijfers werden opgeschoven zodra een persoon bijvoorbeeld geen openstaande storing had. En dan zouden de cijfers in het rapport weer niet met mekaar overeenkomen.

[ACM]

Volgens mij kan dat wel een stukje efficienter dan met 4 subqueries:

SQL:

SELECT qry_OpenstaandeTickets_2.spec1 AS Specialist,
Count(qry_OpenstaandeTickets_2.[W/S]) AS [Openstaande Tickets],
SUM(CASE WHEN qry_OpenstaandeTickets_2.[W/S] = "Installatie" 
                     THEN 1 ELSE 0 END) AS [Openstaande Installaties],
etc
FROM qry_OpenstaandeTickets_2
GROUP BY qry_OpenstaandeTickets_2.spec1;

't Kan zijn dat je dbms geen CASE-statement kent of dat je het met een IF/IFF-statement korter kan oplossen. Of dat je dbms zelfs automatisch al een boolean naar een integer cast, waardoor de CASE helemaal niet nodig is.

En de integratie met die select op de HDTickets-tabel lijkt me relatief triviaal met zo'n query. In de FROM/WHERE stop je nu gewoon wat je eigenlijk al eerder had, lijkt me.

[ Voor 16% gewijzigd door ACM op 25-11-2004 11:24 ]

[bigbeng]

En mocht het van ACM niet werken, dan kun je nog altijd verder prutsen met de volgende aangepast query, gebaseerd op jvdmeer's query. Er moesten nog wat haakjes in

code:

SELECT DISTINCT x1.spec1, x1.Openstaand, IIf(Installatie1,Installatie1,0) AS Installatie, IIf(x3.Storing1,x3.Storing1,0) As Storing,IIf( x4.wijziging1, x4.wijziging1, 0 ) As Wijziging
FROM (((SELECT spec1, COUNT([w/s]) As Openstaand
FROM qry_openstaandetickets2
GROUP BY spec1
) x1 LEFT JOIN (SELECT spec1, count(*) As Installatie1
FROM qry_OpenstaandeTickets2
WHERE [w/s]="Installatie"
GROUP BY spec1
)  x2 ON x1.spec1 = x2.spec1) LEFT JOIN (SELECT spec1, count(*) As Storing1
FROM qry_OpenstaandeTickets2
WHERE [w/s]="Storing"
GROUP BY spec1
) x3 ON x1.spec1 = x3.spec1) LEFT JOIN 
(
SELECT spec1, count(*) As Wijziging1
FROM qry_OpenstaandeTickets2
WHERE [w/s]="Wijziging"
GROUP BY spec1
) x4
ON x1.spec1 = x4.spec1;

En je mag blij zijn dat er nog wat Tweakers voor je willen puzzelen, want eigenlijk zou je dit gepruts natuurlijk op eigen kracht moeten uitvinden.
De JOIN syntax, inclusief JOINS over meerdere tabellen, kun je vinden in het JET SQL gedeelte van de helpfile van Access. Daar wordt onder andere uitgelegd hoe de haakjes moeten staan. Succes!

[Verwijderd]

bigbeng schreef op donderdag 25 november 2004 @ 12:30:
En mocht het van ACM niet werken, dan kun je nog altijd verder prutsen met de volgende aangepast query, gebaseerd op jvdmeer's query. Er moesten nog wat haakjes in

code:

SELECT DISTINCT x1.spec1, x1.Openstaand, IIf(Installatie1,Installatie1,0) AS Installatie, IIf(x3.Storing1,x3.Storing1,0) As Storing,IIf( x4.wijziging1, x4.wijziging1, 0 ) As Wijziging
FROM (((SELECT spec1, COUNT([w/s]) As Openstaand
FROM qry_openstaandetickets2
GROUP BY spec1
) x1 LEFT JOIN (SELECT spec1, count(*) As Installatie1
FROM qry_OpenstaandeTickets2
WHERE [w/s]="Installatie"
GROUP BY spec1
)  x2 ON x1.spec1 = x2.spec1) LEFT JOIN (SELECT spec1, count(*) As Storing1
FROM qry_OpenstaandeTickets2
WHERE [w/s]="Storing"
GROUP BY spec1
) x3 ON x1.spec1 = x3.spec1) LEFT JOIN 
(
SELECT spec1, count(*) As Wijziging1
FROM qry_OpenstaandeTickets2
WHERE [w/s]="Wijziging"
GROUP BY spec1
) x4
ON x1.spec1 = x4.spec1;

En je mag blij zijn dat er nog wat Tweakers voor je willen puzzelen, want eigenlijk zou je dit gepruts natuurlijk op eigen kracht moeten uitvinden.
De JOIN syntax, inclusief JOINS over meerdere tabellen, kun je vinden in het JET SQL gedeelte van de helpfile van Access. Daar wordt onder andere uitgelegd hoe de haakjes moeten staan. Succes!

Super dat ik zo wordt geholpen, het werkt perfect! hulde!

[dip]

Er bestaat een handig tooltje waarmee je heel eenvoudig dikke mysql queries kan maken. Genaamd EMS MySQL Manager.

Dit programma maakt verbinding met je mysql database en selecteerd de tabellen. Vervolgens kun je zoals in mssql server of access velden selecteren en voorwaarden aangeven.

Ideaal als je niets van mysql snapt echter een beetje zelf ploeteren kan nooit kwaad

[bigbeng]

dip schreef op donderdag 25 november 2004 @ 12:39:
Er bestaat een handig tooltje waarmee je heel eenvoudig dikke mysql queries kan maken. Genaamd EMS MySQL Manager.

Dit programma maakt verbinding met je mysql database en selecteerd de tabellen. Vervolgens kun je zoals in mssql server of access velden selecteren en voorwaarden aangeven.

Ideaal als je niets van mysql snapt echter een beetje zelf ploeteren kan nooit kwaad

Handige tip, de TS gebruikt Access

@TS: Oh ja, als je die cases van ACM vervangt door IIf statements zoals in mijn query, dan doet ie het ook... (hoezo ik heb tijd over op mijn werk )

[ACM]

Tuurlijk doet ie het
En ik gok dat je niet veel efficienter kan aggregeren dan alles in 1x te doen.

[bigbeng]

ACM schreef op donderdag 25 november 2004 @ 14:21:
Tuurlijk doet ie het
En ik gok dat je niet veel efficienter kan aggregeren dan alles in 1x te doen.

Ik kon het verschil in performance niet goed bepalen met mijn voorbeeld data. Het ging dan ook om 4 records