SQL 5 verschillende counts uit 1 kolom in 1 query

[arnietjez]

Hallo,

Er moet een query komen waarin ik voor elke cursus het aantal cursisten tel, ik heb hier 5 verschillende cursussen in 1 kolom staan, het aantal cursisten dat deze cursus volgt is af te lezen door het aantal keren dat de cursus voor komt in deze kolom te tellen.
Wie kan mij verder helpen?

ik snap hoe ik er 1 hiervan kan tellen maar hoe kan ik er meerdere tellen in 1 query? dus meerdere counts uit 1 kolom in 1 query, in 1 sql command.

Alvast vriendelijk bedankt.

[Voutloos]

Laat je datamodel eens concreet zien, want het klinkt alsof er iets serieus aan rammelt.

[glmona]

of je weet niet wat GROUP BY doet

maar inderdaad, het lijkt erop dat het datamodel niet klopt

[ Voor 48% gewijzigd door glmona op 08-07-2009 11:53 ]

[Cascade]

SQL:

SELECT cursus, COUNT(*) FROM cursisten GROUP BY cursus ORDER BY cursus;

[arnietjez]

[arnietjez]

ik denk dat me datamodel niet klopt volgens jullie normen maar ik dacht dat het wel mogelijk zou zijn om deze te counten op aantal "fabu"aantal "auca" etc.

[Jaap-Jan]

Nee. En dit soort vragen zijn nou net de reden waarom normaliseren zo belangrijk is.

[arnietjez]

ik heb genormaliseerd en het is gecontroleerd en verbetert maar blijkbaar niet goed genoeg....
jullie snappen nu wat ik bedoel, heb je geen idee hoe ik het alsnog zou kunnen counten? of moet ik 5 verschillende queries gaan maken met elk count where productcode like "fabu*"? etc..?

[HuHu]

arnietjez schreef op woensdag 08 juli 2009 @ 12:06:
ik heb genormaliseerd en het is gecontroleerd en verbetert maar blijkbaar niet goed genoeg....
jullie snappen nu wat ik bedoel, heb je geen idee hoe ik het alsnog zou kunnen counten? of moet ik 5 verschillende queries gaan maken met elk count where productcode like "fabu*"? etc..?

Probeer die query van Cascade eens, die lijkt me te doen wat je wilt.

[ValHallASW]

Jaap-Jan schreef op woensdag 08 juli 2009 @ 12:00:
Nee. En dit soort vragen zijn nou net de reden waarom normaliseren zo belangrijk is.

Normalisatie maakt voor deze vraag absoluut niets uit. De query zoals Cascade die geeft zou hier prima moeten werken... (in een genormaliseerd model was dit de kruistabel cursus_has_cursist geweest, en daar zou je ook gewoon een count cursist group by cursus doen!)

[arnietjez]

De query van Cascade werkt inderdaad, maar nu staan er meerdere "Fabu" cursussen, zou ik deze "FABU aa" "Fabu ab" etc. als 1 fabu kunnen weergeven?

[tss68nl]

Jaap-Jan schreef op woensdag 08 juli 2009 @ 12:00:
Nee. En dit soort vragen zijn nou net de reden waarom normaliseren zo belangrijk is.

Je zet hem op het verkeerde been. In een genormaliseerd schema zou je de twee tabellen joinen en vervolgens zou de output van die join er exact hetzelfde uit zien als zijn tabel zoals die nu is opgeslagen, alleen niet materialized.

De query zoals hierboven gegeven zou dan ook in beide gevallen het correcte resultaat opleveren. Wil je niet alleen over het cursustype tellen, maar ook differentieren naar datum, dan doe je een 'group by productcode, cursusdatum'

[Jaap-Jan]

arnietjez schreef op woensdag 08 juli 2009 @ 12:16:
De query van Cascade werkt inderdaad, maar nu staan er meerdere "Fabu" cursussen, zou ik deze "FABU aa" "Fabu ab" etc. als 1 fabu kunnen weergeven?

Als het allemaal vierlettercodes zijn, kun je misschien iets doen met de SUBSTR() functie in de GROUP BY clause, maar echt netjes is het nog steeds niet.

tss68nl schreef op woensdag 08 juli 2009 @ 12:21:
[...]


Je zet hem op het verkeerde been. In een genormaliseerd schema zou je de twee tabellen joinen en vervolgens zou de output van die join er exact hetzelfde uit zien als zijn tabel zoals die nu is opgeslagen, alleen niet materialized.

De query zoals hierboven gegeven zou dan ook in beide gevallen het correcte resultaat opleveren. Wil je niet alleen over het cursustype tellen, maar ook differentieren naar datum, dan doe je een 'group by productcode, cursusdatum'

Klopt, maar dit is wel de manier waarop het in de tabel staat, dus je hebt zonder duidelijke reden dubbele informatie en (maar dat kon ik niet van tevoren weten) ProductCode is blijkbaar een soort van
'magische string', getuige de vraag die hierboven gesteld wordt, dus nog steeds geen geweldig datamodel.

[ Voor 78% gewijzigd door Jaap-Jan op 08-07-2009 12:27 ]

[tss68nl]

arnietjez schreef op woensdag 08 juli 2009 @ 12:16:
De query van Cascade werkt inderdaad, maar nu staan er meerdere "Fabu" cursussen, zou ik deze "FABU aa" "Fabu ab" etc. als 1 fabu kunnen weergeven?

Dan zal je je productcode moeten splitsen. Dit is wel een normalisatie probleem daar je hoofd product code en de subcode beter in gescheiden kolommen had kunnen opslaan. als je hoofdcode altijd 4 karakters lang is, kan je:

select left(ProductCode,4) as ProductHCode, count(*) as AantalCursisten
from cursisten
group by left(ProductCode,4)
order by left(ProductCode,4)

Dit kan zelfs nog iets netter met een subselect, maar daar ga ik je niet teveel mee vermoeien, op 3 plekken de left berekenen is acceptabel. Beter kan je zoals gezegd de velden splitsen in de database.

[arnietjez]

Heel erg bedankt! hij doet het :D!!!!!!!!!!!!!!! bedankt!

[Jaap-Jan]

Ga nog wel even kijken naar je datamodel, string- operaties zoals left() zijn best duur en de fix is best makkelijk.

[ Voor 13% gewijzigd door Jaap-Jan op 08-07-2009 12:49 ]

[arnietjez]

Het datamodel is op het moment nog goed en werkend dus daar ben ik nog niet van plan om iets in aan te passen.
SELECT left([ProductCode],4) AS ProductHCode, count(*) AS AantalCursisten
FROM deelname
GROUP BY left([ProductCode],4)
ORDER BY left([ProductCode],4);

dit is hem uiteindelijk geworden.
nu moet ik nog een query maken die per locatie per cursus het aantal deelnemers toont aan die cursus, ook een som van het totale cursusbedrag.
maar nu met deze left query lukt het me niet om nog een nieuwe kolom er bij in te selecten, daarna moet ik overigens ook nog een andere tabel en een rij daar van bij elkaar optellen, individueel lukken deze dingen mij normaal gesproken wel, maar het "left" command lijkt dit te blokkeren? hij komt met een foutmelding over een niet toegewezen statische functie..

[Voutloos]

arnietjez schreef op woensdag 08 juli 2009 @ 14:03:
Het datamodel is op het moment nog goed en werkend dus daar ben ik nog niet van plan om iets in aan te passen.

Als je datamodel goed was, was dit topic er niet geweest. Idem voor je 2e vraag.

[arnietjez]

Voutloos schreef op woensdag 08 juli 2009 @ 14:05:
[...]
Als je datamodel goed was, was dit topic er niet geweest. Idem voor je 2e vraag.

heel leuk dat me datamodel niet goed genoeg voor jou is, helaas voor jou is dit volgens anderen nog niet zo drastisch en is er nog mee te werken. dus wil je aub je kritiek voor je houden, aangezien dit nutteloze en incorrecte kritiek is.
bedankt.

[Remus]

arnietjez schreef op woensdag 08 juli 2009 @ 14:21:
[...]

heel leuk dat me datamodel niet goed genoeg voor jou is, helaas voor jou is dit volgens anderen nog niet zo drastisch en is er nog mee te werken. dus wil je aub je kritiek voor je houden, aangezien dit nutteloze en incorrecte kritiek is.
bedankt.

Jij bent diegene die hier om hulp komt. Je kan niemand ervan weerhouden om je ontwerpkeuzes te bekritiseren, net zo min als wij je ervan kunnen weerhouden om niet naar ons te luisteren.

Over je probleem: misschien moet je even goed nalezen wat de werking van GROUP BY is en welke eisen daaraan verbonden zijn.

Als je er daarmee niet uitkomt, post dan de query die je probeert, de database server die je gebruikt en de exacte foutmelding.

[Creepy]

arnietjez schreef op woensdag 08 juli 2009 @ 14:21:
[...]

heel leuk dat me datamodel niet goed genoeg voor jou is, helaas voor jou is dit volgens anderen nog niet zo drastisch en is er nog mee te werken. dus wil je aub je kritiek voor je houden, aangezien dit nutteloze en incorrecte kritiek is.
bedankt.

De kritiek die je hier op je datamodel krijgt is volledig terecht. Het is ook allemaal goed bedoeld advies. Geirriteerd hierdoor raken is dan ook echt niet nodig.

Als je met string functies aan de slag moet voor vrij eenvoudige zaken dan rammelt er wat, ook in dit geval. Ik heb geen idee wie die andere personen zijn waar je het over hebt maar als ze echt verstand hebben van normaliseren e.d. en je legt ze je huidige problemen en de hier aangedragen oplossing voor dan zullen zij ook zeggen dat je datamodel niet goed is en een aanpassing noodzakelijk is voor dit soort queries.

Als de cursusnaam AuCa is dan moet je geen AuCa AA opslaan. Blijkbaar is die AA een type van een cursus, sla dat dan ook als type kolom bij de cursus op. Daarnaast sla je nu meerdere gegevens van dezelfde cursus meerdere malen op bij een cursist. Dus als je echt aan het normaliseren bent geweest is het handig om de 1ste en 2de normaalvorm er nog eens bij te pakken.

Neem dit aub van me aan: met een goed datamodel zijn de queries die je nu wilt maken simpel.

[ Voor 6% gewijzigd door Creepy op 08-07-2009 15:03 ]

[arnietjez]

Creepy schreef op woensdag 08 juli 2009 @ 15:00:
[...]

De kritiek die je hier op je datamodel krijgt is volledig terecht. Het is ook allemaal goed bedoeld advies. Geirriteerd hierdoor raken is dan ook echt niet nodig.

Als je met string functies aan de slag moet voor vrij eenvoudige zaken dan rammelt er wat, ook in dit geval. Ik heb geen idee wie die andere personen zijn waar je het over hebt maar als ze echt verstand hebben van normaliseren e.d. en je legt ze je huidige problemen en de hier aangedragen oplossing voor dan zullen zij ook zeggen dat je datamodel niet goed is en een aanpassing noodzakelijk is voor dit soort queries.

Als de cursusnaam AuCa is dan moet je geen AuCa AA opslaan. Blijkbaar is die AA een type van een cursus, sla dat dan ook als type kolom bij de cursus op. Daarnaast sla je nu meerdere gegevens van dezelfde cursus meerdere malen op bij een cursist. Dus als je echt aan het normaliseren bent geweest is het handig om de 1ste en 2de normaalvorm er nog eens bij te pakken.

Neem dit aub van me aan: met een goed datamodel zijn de queries die je nu wilt maken simpel.

oke dan neem ik dat van je aan
en dat AA AB etc heb ik gedaan om de cursus per datum afgescheiden te houden, achteraf niet zo handig nee... maarja... heb de hoop al opgegeven

[DaCoTa]

arnietjez schreef op woensdag 08 juli 2009 @ 15:45:
[...]
oke dan neem ik dat van je aan
en dat AA AB etc heb ik gedaan om de cursus per datum afgescheiden te houden, achteraf niet zo handig nee... maarja... heb de hoop al opgegeven

Dan is het redundante data die in dezelfde tabel staat en dat betekend dat de fix kan zonder je datamodel om te gooein. Je hoeft dan alleen maar het redundante data gedeelte weghalen (de AA AB etc) en de queries die daarop vertrouwen aanpassen.

[ Voor 6% gewijzigd door DaCoTa op 08-07-2009 15:55 ]

[jbdeiman]

@arnietjez
Nu snap ik je niet meer, je slaat een datum op bij de verschillende cursussen, maar je gooit er AA, AB enz. achter (achter de naam) om de cursus per datum afgescheiden te houden. Je kan ook groeperen op 2 kolomen (cursusnaam/ datum) en voila, dan heb je de cursussen gegroepeerd op datum en op cursusnaam.

De hoop opgeven is helemaal niet nodig, met een paar simpele aanpassingen (makkelijkst is denk ik 1 kolom aanmaken, cursuscode en een UPDATE tabelnaam SET cursuscode = LEFT(ProductCode,4) query uitvoeren. ZO krijg je alvast de 1e 4 tekens van de Productcode in een nieuwe kolom. Indien nodig kan je dit ook doen voor de laatste 2 tekens (die staan voor datum) maar voor zover ik je nu begreep is dat helemaal niet nodig.

[Creepy]

DaCoTa: dan laat je wel achterwege dat een extra tabel handig is (twee extra tabellen eigenlijk, of 3 zelfs als je locatie meer is dan alleen een naam ....) omdat er nog wat meer redundante data in zit

[ Voor 74% gewijzigd door Creepy op 08-07-2009 16:59 ]

[arnietjez]

Ik moet per locatie per cursus het aantal deelnemers aan die cursus tellen, ik ga mijn database niet aanpassen. is iemand mij van plan te helpen of wordt ik alleen afgekraakt hier?

[Voutloos]

Dat antwoord heb je nu toch al? Groepeer op cursus en tijdstip en klaar ben je.

Als je maar inziet dat dit de korte termijn oplossing is. En dat je dus waarschijnlijk bij de eerst volgende query op die tabel weer een truc kan bedenken etc etc.

offtopic:
En kritiek != afkraken. Kritiek == opbouwend.

[arnietjez]

Voutloos schreef op woensdag 08 juli 2009 @ 17:14:
Dat antwoord heb je nu toch al? Groepeer op cursus en tijdstip en klaar ben je.

Als je maar inziet dat dit de korte termijn oplossing is. En dat je dus waarschijnlijk bij de eerst volgende query op die tabel weer een truc kan bedenken etc etc.

offtopic:
En kritiek != afkraken. Kritiek == opbouwend.

dat dit een korte termijn oplossing is had ik al ingezien ja maar dat is niet erg aangezien dit een eenmalig project is

[arnietjez]

hij werkt goed met deze code:
SELECT deelname.locatienaam, left([deelname.ProductCode],4) AS cursus, count(*) AS AantalCursisten
FROM deelname
GROUP BY deelname.locatienaam, left([deelname.ProductCode],4)
ORDER BY left([deelname.ProductCode],4);

totdat ik de tabel "cursus" er aan toevoeg bij FROM, voor het toevoegen van de prijzen...als ik dit doe komt die namelijk ineens met gigantische getallen bij het aantal cursisten.

[CH4OS]

Als je bij de kolommen die je wilt hebben kolomnaam.tabelnaam doet en na FROM alle tabellen noemt zou het toch moeten werken lijkt me.

[Jaap-Jan]

Dat is basis SQL. Als je meer tabellen toevoegt in de FROM- clause, krijg je een carthesisch product waarbij alle mogelijke combinaties uit deelname en cursisten worden gejoind. Die mogelijke combinaties moet je beperken in je WHERE- clause. Hoe dat moet, moet je zelf maar eens proberen.

En alsjeblieft, reageer iets minder bijdehand. We proberen je hier proberen je ook maar te helpen, maar als gebruikers worden afgezeken worden als ze iets zeggen, is de lol er al snel vanaf.

[arnietjez]

ja dat leek mij ook, maar als ik met deze code alleen "cursus" toevoeg in from begint die al raar te doen....

[Creepy]

Ik heb gelijk even wat offtopic zaken verwijderd. Hou het gezellig aub. arnietjez: je wordt niet afgekraakt, zolang je inderdaad maar beseft dat je oplossing niet optimaal is. Ik weet niet hoe je erop beoordeeld gaat worden maar als je docent wat verstand van zaken heeft dan meld die hetzelfde als wij

Je problemen blijven voortkomen uit een niet optimale database en zo te zien ook omdat je kennis van SQL niet helemaal is wat het had kunnen zijn. Je wilt niet zomaar een tabel aan je from toevoegen. Zorg dat dat met een JOIN gebeurd (en wat een JOIN is staat o.a. uitgelegd in Programming FAQ - SQL

[farlane]

Ik snap niet goed dat je kennelijk bij de cursist hebt ingezien dat er aparte tabel met cursisten moet zijn en een FK in je tabel moet, en bij de te volgens cursus niet.

[arnietjez]

het is me gelukt
SELECT deelname.LocatieNaam, Left([deelname.ProductCode],4) AS Cursus, Count(*) AS AantalCursisten, Sum(Cursus.CursusPrijs) AS TotaalCursusbedrag
FROM Cursus INNER JOIN deelname ON Cursus.ProductCode = deelname.ProductCode
GROUP BY deelname.LocatieNaam, Left([deelname.ProductCode],4), Cursus.CursusPrijs
ORDER BY Left([deelname.ProductCode],4);

[arnietjez]

nog een vraagje , ik heb een bijwerkquery, deze verlaagt alle FABU cursussen met 3%, maar deze zelfde query moet alle andere cursussen, zonder FABU in de productcode dus, verhogen met 7%.
is er iets van een NOT having command in de bijwerk queries van SQL ofzo?

[Creepy]

Pak er een tweede query voor zou ik zeggen. NOT bestaat, HAVING ook, maar of dat bruikbaar is voor wat je wil betwijfel ik. Maar dat kan je prima zelf uitzoeken. Dus wat heb je al geprobeerd en wat lukte daar niet mee? (dat zou je bij elke vraag hier moeten vermelden )

[ Voor 37% gewijzigd door Creepy op 08-07-2009 19:46 ]

[arnietjez]

het moet in 1 query , ik ga het wel ff proberen met NOT

[farlane]

Is dit eigenlijk een hobbyproject of wordt dit ook ingezet in de praktijk? Ik denk nl in dat geval dat je je klant moet duidelijk maken dat er nog veel meerwerk aan zit te komen.

[arnietjez]

UPDATE cursus SET cursus.CursusPrijs = CursusPrijs*0.97
WHERE (((cursus.ProductCode) Like "FABU*"))
AND
UPDATE cursus SET cursus.cursusPrijs = Cursusprijs*1.07
WHERE cursus.ProductCode NOT LIKE "Fabu*"
;

wat doe ik fout?
"(operator ontbreekt)"

[arnietjez]

farlane schreef op woensdag 08 juli 2009 @ 19:57:
Is dit eigenlijk een hobbyproject of wordt dit ook ingezet in de praktijk? Ik denk nl in dat geval dat je je klant moet duidelijk maken dat er nog veel meerwerk aan zit te komen.

schoolproject

[Creepy]

arnietjez schreef op woensdag 08 juli 2009 @ 19:58:
UPDATE cursus SET cursus.CursusPrijs = CursusPrijs*0.97
WHERE (((cursus.ProductCode) Like "FABU*"))
AND
UPDATE cursus SET cursus.cursusPrijs = Cursusprijs*1.07
WHERE cursus.ProductCode NOT LIKE "Fabu*"
;

wat doe ik fout?
"(operator ontbreekt)"

Ik had je al geprobeerd uit te leggen dat het zo hier niet werkt. Lees aub het PRG beleid eens door, die sticky topics staan er niet voor niets. Dit is zo echt niet de bedoeling