[sql] Herkent kolom alias niet bij GROUP BY

[shades]

Ik heb een query die er als volgt uit ziet:

SQL:

SELECT VeldId, VeldNaam, SUM(DBO.fn_CountFunctie(tblMeuk.VeldId)) As [Aantal]  FROM tblMeuk
GROUP BY Aantal

Sql geeft terug 'Invalid column name: Aantal'

Iemand een idee hoe ik dit kan oplossen ?

[P_de_B]

Dit klopt ook niet. Je moet juist de kolommen die niet in een aggregate functie zijn opgenomen in je gourp by zetten.

GROUP BY VeldId, Veldnaam

Duidelijke uitleg: P&W FAQ - SQL

[Woy]

IDD en in Group by clause kun je trouwens meestal geen alias gebruiken.Je zult dan nogmaals het hele statement moeten herhalen.

[NMe]

P&W FAQ - SQL: Hoe werkt dat GROUP BY nu eigenlijk?

edit:
Hmm, ik moet toch even refreshen voor ik post.

[ Voor 28% gewijzigd door NMe op 19-08-2005 11:55 ]

[Skaah]

GROUP BY 3

[whoami]

GROUP BY 3 zal in dit geval ook niet werken, aangezien je dan ook op het aggregate field groepeert, terwijl je op die andere 2 moet groeperen.

[shades]

Ah, dan heb ik het inderdaad niet helemaal begrepen. Goed dat er verwezen wordt naar de FAQ. Nu alleen nog die overbodige nulls weg zien te krijgen.. Ik wil namelijk alleen het eindtotaal erbij en niet die overige

[ Voor 19% gewijzigd door shades op 19-08-2005 12:51 ]

[Verwijderd]

Ik ken je data structuur en doel van je query niet, maar met HAVING of WHERE zou dat best moeten lukken, schat ik zo. Succes

[shades]

Verwijderd schreef op vrijdag 19 augustus 2005 @ 12:55:
Ik ken je data structuur en doel van je query niet, maar met HAVING of WHERE zou dat best moeten lukken, schat ik zo. Succes

Mmm, nee.
De <null>'s worden veroorzaakt door de subtotals (die ik dus niet wil) en het grant total (die ik dus wel wil) maar ik ben bang dat ik dat in de code moet afvangen of ik moet een COMPUTE gebruiken maar dan krijg ik 2 sets terug en ik weet niet zo goed hoe ik dat in c# moet oppikken (via dataset ofzo ?)

[P_de_B]

Laat eens zien hoe je tabel eruit ziet, en welke resultaten je graag wilt hebben.

[shades]

P_de_B schreef op vrijdag 19 augustus 2005 @ 13:10:
Laat eens zien hoe je tabel eruit ziet, en welke resultaten je graag wilt hebben.

Dat is een beetje ingewikkeld omdat ik de bovenstaande functie sterk vereenvoudigd heb. Het aantal wordt m.b.v. een funtie opgehaald (staat gewoon een simpele COUNT(*) in maar om dat SUM en COUNT niet samen gaan heb ik een functie hiervoor moeten moeten.

code:

VeldId  VeldNaam               Aantal      
------- ---------------------- ----------- 
1       Gebruiker1.            49
1       NULL                   49
2       Gebruiker2             8
2       NULL                   8
3       Gebruiker3             0
3       NULL                   0
8       Gebruiker4             47
8       NULL                   47
27      Gebruiker5             0
27      NULL                   0
NULL    NULL                   104

(11 row(s) affected)

Wat is dus eigenlijk wil hebben is

code:

VeldId  VeldNaam               Aantal      
------- ---------------------- ----------- 
1       Gebruiker1.            49
2       Gebruiker2             8
3       Gebruiker3             0
8       Gebruiker4             47
27      Gebruiker5             0
NULL    NULL                   104

(of daar waar NULL staat bij VeldNaam 'Totaal'.)

[P_de_B]

shades schreef op vrijdag 19 augustus 2005 @ 13:23:
[...]
)

Ik heb het idee dat je een beetje verkeerd zit te werken, kun je toch een tablelayout en de query die je nu gebruikt posten?

[ Voor 71% gewijzigd door P_de_B op 19-08-2005 13:55 ]

[shades]

Dat zou goed kunnen. Ik ben niet heel goed in sql..

Hier is de tablelayout (het gedeelte wat nodig is)


De functie (DBO.fn_AantalReserveringenAuteur )om het SUM, COUNT(*) probleem te omzeilen

SQL:

DECLARE @Cnt INT
SET @Cnt = NULL
SELECT @Cnt = COUNT(*) FROM tblReserveringen WHERE fk_AuteurId = @AuteurId
RETURN @Cnt

De query

SQL:

SELECT
    AuteurId,
    AuteurNaam,
    SUM(DBO.fn_AantalReserveringenAuteur(tblAuteurs.AuteurId)) As [Aantal]

FROM tblAuteurs

WHERE fk_ZaalEigenaarId = (
    SELECT fk_ZaalEigenaarId
    FROM tblAuteurs
    WHERE AuteurId = @AuteurId
)

GROUP BY AuteurId, AuteurNaam WITH ROLLUP

Waarschijnlijk doe ik te omslachtig. Als het op een andere elegantere manier opgelost kan worden hoor ik dat graag. Leer ik ook weer van.

[ACM]

En beschijf eens in woorden wat die query zou moeten doen??

[shades]

ACM schreef op vrijdag 19 augustus 2005 @ 14:28:
En beschijf eens in woorden wat die query zou moeten doen??

tblReserveringen: Hierin staan reserveringen in voor diverse (vergader)zalen
tblAuteurs: Hier staan de auteurs in
tblZaalEigenaren: Hier staan groepen in.

Er zitten een aantal zalen (tabel niet weergegeven) in een Zaaleigenaar groep en een auteur is lid van 1 van die groepen. Op deze manier kan een auteur maar in een select aantal zalen reserveringen plaatsen. Dit wordt in de code afgehandeld.

Wat ik nu wil is om a.d.v. een Auteur (AuteurId) er achter komen hoeveel reserveringen hij/zij gemaakt heeft in welke zaal dan ook en hoeveel reserveringen de groep (tblZaalEigenaren) heeft gemaakt waar zij deel van uit maakt. Resultaat zoals hier beneden wil ik krijgen:

code:

AuteurId AuteurNaam            Aantal      
-------- --------------------- ----------- 
1        Gebruiker1.           49
2        Gebruiker2            8
3        Gebruiker3            0
8        Gebruiker4            47
27       Gebruiker5            0
NULL     NULL                  104

[P_de_B]

Om het door jou gegeven gewenste resultaat te krijgen kun je eenvoudig het volgende doen:

SQL:

SELECT AuteurId, AuteurNaam, COUNT(*) as Aantal
FROM tblAuteurs A
INNER JOIN tblReserveringen R ON A.AuteurId = R.fk_AuteurId
GROUP BY AuteurId, AuteurNaam

Maar volgens mij komt de door jou gegeven gewenste output niet overeen met hetgeen je beschrijft. Wil je de totalen per zaalgroep in een aparte query, of in deze erbij?

[shades]

Als ik deze query uitvoer mis ik alleen de auteurs die geen reserveringen hebben gemaakt. Dit komt door de INNER JOIN maar als ik ipv een INNER een LEFT JOIN geeft COUNT(*) een 1terwijl dat een 0 zou moeten zijn . Breid ik de query uit zodat ik alleen maar de mensen krijg die ook bij hem/haar in de groep zitten dan mis is die personen dus ook.

SQL:

DECLARE @AuteurId INT
SET @AuteurId = 2
SELECT AuteurId, AuteurNaam, COUNT(*) as Aantal 
FROM tblAuteurs A 
INNER JOIN tblReserveringen R ON A.AuteurId = R.fk_AuteurId 
--LEFT JOIN tblReserveringen R ON A.AuteurId = R.fk_AuteurId
WHERE fk_ZaalEigenaarid = (SELECT fk_ZaalEigenaarId FROM tblAuteurs WHERE AuteurId = @AuteurId)
GROUP BY AuteurId, AuteurNaam
ORDER BY AuteurId

Verder wil ik eigenlijk ook het totaal van die COUNT(*) hebben dus zoiets als maar dan zonder de subtotals

SQL:

DECLARE @AuteurId INT
SET @AuteurId = 2
SELECT AuteurId, AuteurNaam, COUNT(*) as Aantal 
FROM tblAuteurs A 
INNER JOIN tblReserveringen R ON A.AuteurId = R.fk_AuteurId 
WHERE fk_ZaalEigenaarid = (SELECT fk_ZaalEigenaarId FROM tblAuteurs WHERE AuteurId = @AuteurId)
GROUP BY AuteurId, AuteurNaam WITH ROLLUP
ORDER BY AuteurId

[ACM]

COUNT(*) zal per definitie wel 1 of meer teruggeven omdat er toch minstens 1 record is (met een null, ok)
COUNT(tblReserveringen.*) levert vast een beter resultaat op?

Zie ook:

code:

acm=# select * from test;
 a | b
---+---
 1 | 1
 1 | 2
 2 | 3

acm=# select * from testref;
 a
---
 1
 2

acm=# select t.a, count(*), count(r.*) from test t 
               left join testref r on t.b = r.a group by t.a;
 a | count | count
---+-------+-------
 2 |     1 |     0
 1 |     2 |     2

[shades]

ACM schreef op vrijdag 19 augustus 2005 @ 15:44:
COUNT(*) zal per definitie wel 1 of meer teruggeven omdat er toch minstens 1 record is (met een null, ok)
COUNT(tblReserveringen.*) levert vast een beter resultaat op?

Ja ik snap em..

Snap sql2000 deze constuctie wel ?
Als ik dat doen dan levert dat de volgens foutmelding op:

Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near '*'.

SQL:

SELECT AuteurId, AuteurNaam, COUNT(R.*) as Aantal 
FROM tblAuteurs A 
LEFT JOIN tblReserveringen R ON A.AuteurId = R.fk_AuteurId 
GROUP BY AuteurId, AuteurNaam

[ Voor 3% gewijzigd door shades op 19-08-2005 15:57 ]

[Verwijderd]

Raar.

Check of er een probleem is met het gebruik van een alias door de volledige tabelnaam daar neer te zetten, en als dat niet werkt, vervang dat de * door het ID veld van de tblReserveringen tabel.

[RobIII]

IsNull(COUNT(R.*),0) as Aantal

ISNULL
Replaces NULL with the specified replacement value.

Syntax
ISNULL ( check_expression , replacement_value )

Arguments
check_expression

Is the expression to be checked for NULL. check_expression can be of any type.

replacement_value

Is the expression to be returned if check_expression is NULL. replacement_value must have the same type as check_expresssion.

Return Types
Returns the same type as check_expression.

RemarksThe value of check_expression is returned if it is not NULL; otherwise, replacement_value is returned.

[ Voor 92% gewijzigd door RobIII op 19-08-2005 16:17 ]

[shades]

RobIII schreef op vrijdag 19 augustus 2005 @ 16:16:
IsNull(COUNT(R.*),0) as Aantal

[...]

Alleen jammer dat COUNT(R.*) niet door sql2k begrepen wordt

Verwijderd schreef op vrijdag 19 augustus 2005 @ 16:13:
Raar.

Check of er een probleem is met het gebruik van een alias door de volledige tabelnaam daar neer te zetten, en als dat niet werkt, vervang dat de * door het ID veld van de tblReserveringen tabel.

tblReserveringen noch tblReserveringen.ReserveringID werkt maar ReserveringID weer wel. Weer wat geleerd Nu moet ik nog een het totaal van alles hebben. Een WITH ROLLUP zou werken maar dan krijg ik weer die overbodige NULL values. Die dubbele NULL, die moet ik hebben of is het handiger een een extra query die alleen dat doet in het leven te roepen ?

[ Voor 14% gewijzigd door shades op 19-08-2005 16:28 ]

[RobIII]

shades schreef op vrijdag 19 augustus 2005 @ 16:26:
[...]


Alleen jammer dat COUNT(R.*) niet door sql2k begrepen wordt

Die heb ik alleen van een Copy/Paste uit dit topic. Het gaat om de ISNULL, welke de makkelijkste oplossing is voor je probleem...

SQL:

Select X, Y, IsNull(Z,0)
From blabla
Left outer join ...
...
...

Ik paste niet voor niks een stuk uit de books online eronder. Een beetje zelf je grijze massa gebruiken mag wel hoor

[ Voor 15% gewijzigd door RobIII op 19-08-2005 16:34 ]

[EfBe]

totaal van een column: COUNT(DISTINCT colname). Ik begrijp overigens echt geen r**t van wat je wilt doen/bereiken, behalve dan dat ik DENK dat je je halve database wilt aggregaten in een 2D table, maar dat gaat veelal niet.

[ACM]

RobIII schreef op vrijdag 19 augustus 2005 @ 16:16:
IsNull(COUNT(R.*),0) as Aantal

I.t.t. de andere aggregate-functies geeft COUNT per definitie 0 of meer terug
Dus je isnull is tamelijk zinloos, het probleem was juist dat er 1 uitkwam of een syntax-error.

Anyway, postgresql vond die count(r.*) wel best (en zou ook prima gewerkt hebben met count(r.id)) en ik heb geen SQL Server bij de hand om alles wat ik wil daarop te testen

shades schreef op vrijdag 19 augustus 2005 @ 16:26:
tblReserveringen noch tblReserveringen.ReserveringID werkt maar ReserveringID weer wel. Weer wat geleerd Nu moet ik nog een het totaal van alles hebben. Een WITH ROLLUP zou werken maar dan krijg ik weer die overbodige NULL values. Die dubbele NULL, die moet ik hebben of is het handiger een een extra query die alleen dat doet in het leven te roepen ?

Waarschijnlijk moet je dat in losse queries uitvoeren. Maar kijk nog even naar dit topic voor wat gespeel met aggregates:
[rml][ mysql] punten optellen[/rml]

[shades]

EfBe schreef op vrijdag 19 augustus 2005 @ 16:38:
totaal van een column: COUNT(DISTINCT colname). Ik begrijp overigens echt geen r**t van wat je wilt doen/bereiken, behalve dan dat ik DENK dat je je halve database wilt aggregaten in een 2D table, maar dat gaat veelal niet.

Euh ja, zoiets maar dat dat niet gaat ben ik middels dit topic een beetje achter gekomen.

ACM schreef op vrijdag 19 augustus 2005 @ 18:29:
[...]

I.t.t. de andere aggregate-functies geeft COUNT per definitie 0 of meer terug
Dus je isnull is tamelijk zinloos, het probleem was juist dat er 1 uitkwam of een syntax-error.

Anyway, postgresql vond die count(r.*) wel best (en zou ook prima gewerkt hebben met count(r.id)) en ik heb geen SQL Server bij de hand om alles wat ik wil daarop te testen


[...]

Waarschijnlijk moet je dat in losse queries uitvoeren. Maar kijk nog even naar dit topic voor wat gespeel met aggregates:
[rml][ mysql] punten optellen[/rml]

Ik maak wel een losse querie voor die laatste. Dat topic is inderdaad een erg leuke om doorgelezen te hebben. Gelijk bookmark van gemaakt. Ik ben in iedergeval heel wat wijzer geworden.

[ Voor 51% gewijzigd door shades op 19-08-2005 22:59 ]

[EfBe]

Nou het gaat wel, maar niet met straight forward queries. Je zou in de OLAP cube queries kunnen gaan kijken, je gebruikt tenslotte toch SQLServer. Even books online erbij en dan heb je zo je 1e cube query gemaakt, waarbij het je waarschijnlijk wel lukt deze data te verzamelen in 1 lijst.

[shades]

EfBe schreef op zaterdag 20 augustus 2005 @ 11:11:
Nou het gaat wel, maar niet met straight forward queries. Je zou in de OLAP cube queries kunnen gaan kijken, je gebruikt tenslotte toch SQLServer. Even books online erbij en dan heb je zo je 1e cube query gemaakt, waarbij het je waarschijnlijk wel lukt deze data te verzamelen in 1 lijst.

Phoe, das best wel onbekend terrein. Ik ga in iedergeval wel een poging wagen.