[SQL] Hoe los ik dit op? (Sum)

[Wijnbo]

Pseudo situatie :

code:

a b c d e

1 a 9 3 1
1 b 9 5 1
2 a 8 7 1
3 a 5 5 2
4 a 7 3 2
4 b 7 4 2

Kolom A geeft een bepaald nummer aan.
Kolom B geeft aan dat iets uit 2 delen bestaat
Kolom C geeft aan de verwachte waarde.
Kolom D geeft aan de werkelijke waarde.
Kolom E geeft aan dat iets van een bepaald type is.

Probleem : De verwachte waarde (c) staat er "dubbel" in bij gesplitste records. Dus het totaal van de 2 gesplitste in plaats van per record.

Wat ik wil is deze uitkomst :

code:

c  d  e
17 15 1
12 12 2

Dus de eerste result row is nummer 1 en 2, de tweede is nummer 3 en 4.

Ik wil dus een SUM van kolom c en d, met een group by op e, het type dus.

Het probleem is dat de SUM dus de "gesplitste" records dubbel telt op de kolom c. Dit is namelijk het totaal van de 2 "gesplitste" records.

Ik kan aan de data niets veranderen, heb al iets geprobeerd met een sub-select maar die vernaggeld mijn group-by waardoor alles weer gesplitst wordt

Dit heb ik nu:

SQL:

SELECT  SUM(x.c) AS c,
        SUM(x.d) + ISNULL((SELECT SUM(y.d) FROM dbo.Table_1 y WHERE x.a = y.a AND y.b = 'b'),0) AS d ,
        x.e
FROM    dbo.Table_1 X
WHERE x.b = 'a'
GROUP BY x.e, x.a

Dit geeft echter als resultaat:

code:

c    d    e
9   8   1
8   7   1
5   5   2
7   7   2

Logisch, want de group- by van x.a veroorzaakt dit. Toch heb ik die wel nodig want anders werkt de sum niet. Hoe los ik dit op?

[Voor 4% gewijzigd door Wijnbo op 26-05-2011 13:26]

[BasieP]

oke omdat ik niets van je verhaal snap hier zoals ik denk dat jij het bedoelt.. ofzo

je hebt een tabel, met daarin 5 kolommen.

Kolom A geeft een bepaald nummer aan. <-- wtf? hoe bepaald nummer? is dit relevant? wil je er wat mee?
Kolom B geeft aan dat iets uit 2 delen bestaat <-- iets'? dus dit is een soort verdeling?
Kolom C geeft aan de verwachte waarde.
Kolom D geeft aan de werkelijke waarde.
Kolom E geeft aan dat iets van een bepaald type is. <-- ah das duidelijk een splitsing. (type 1 of type 2)

Probleem : De verwachte waarde staat er "dubbel" in bij gesplitste records.

wat is een splitsing? 2 verschillende typen? of iets met die kolommen a en b?

wil je van de kolommen C en D (verwachte waarde en werkelijke waarde) het totaal hebben per type?
of per splitsing?

als ik je sql voorbeeld lees gebruik je b alleen in je where clause, dus die is eigenlijk helemaal niet relevant.

ik zie dat je a gebruikt als soort van join tussen 2x dezelfde tabel?

wellicht is het voor het voorbeeld hier wel handig om isnull controlles enzo (en dbo.) te verwijderen uit je voorbeeld. Maakt het een stuk makkelijker leesbaar.

Verder vind ik je variabelen naamgeving brilliant. Dat schept gelukkig geen verwarring. Ik noem mijn variabelen ook altijd a-z. En als die op zijn gebruik ik hoofdletters.

---
samenvatting:
wat bedoel je precies?

[Skinny]

aan de tabelnaam (dbo.x) te zien gebruik je MSSQL. Volgens mij kun je gewoon doen :

SQL:

SELECT SUM(DISTINCT c), SUM(d), e
FROM pseudo
GROUP BY e

Daarmee krijg ik het resultaat wat jij wil. De DISTINCT zorgt ervoor dat er per GROUP alleen de unieke waarden worden geSUMmed.

zie ook : MSDN: SUM (Transact-SQL)

Ik ga er gemakshalve dus even vanuit dat voor kolom C geldt dat er per combinatie van E/B steeds dezelfde C voorkomt


@hieronder : ik zie het inderdaad.. maar die had ik nu even niet zo snel beschikbaar :-)

[Voor 32% gewijzigd door Skinny op 26-05-2011 13:38]

[thioz]

@Skinny ... werkt ook gewoon in MySql

Maar @Wijnbo ... nu is nog eigenlijk de beste vraag ... wat is/doet het ?

[Voor 50% gewijzigd door thioz op 26-05-2011 13:38]

[Anoniem: 167624]

Maar daarbij filter je niet op kolom B, wat TS in de originele query lijkt te proberen. Mocht dat nodig zijn dan is iets als dit misschien een oplossing:

SQL:

SELECT (SELECT SUM(c) FROM test WHERE b='a' AND e=test2.e) AS C,
       SUM(d) AS D,
       e
FROM test AS test2
GROUP BY e

Ik moet er wel bij zeggen dat de query van Skinny een stuk netter is als dat ook aan de specificatie van je probleem voldoet.

[jmzeeman]

Met een subquery waarin je de op a gegroepeerde waardes in hebt staan?

Zo bijvoorbeeld:

SQL:

select e, sum(c2), sum(d2)
from (select a,e, first(c) as c2, sum(d) as d2 from table1 group by a,e)
group by e

Ik gebruik hier first om de verwachte waarde te pakken omdat deze als ik je data goed begrijp voor een combinatie a,e altijd het zelfde is (mocht dat niet zo zijn kan je natuurlijk een avg, min of max gebruiken afhankelijk van wat van toepassing is), d sum ik eerst per a en in de omvattende query op e.

[Voor 11% gewijzigd door jmzeeman op 26-05-2011 14:07]

[Big Womly]

BasieP schreef op donderdag 26 mei 2011 @ 13:34:
Kolom A geeft een bepaald nummer aan. <-- wtf? hoe bepaald nummer? is dit relevant? wil je er wat mee?
Kolom B geeft aan dat iets uit 2 delen bestaat <-- iets'? dus dit is een soort verdeling?
Kolom C geeft aan de verwachte waarde.
Kolom D geeft aan de werkelijke waarde.
Kolom E geeft aan dat iets van een bepaald type is. <-- ah das duidelijk een splitsing. (type 1 of type 2)

---
samenvatting:
wat bedoel je precies?

Ik volg je in je samenvatting

Wat hij met kolom A en B bedoeld, is volgens mij dat 1 een soort van ID is, en B een sequentie.
Stel dat je een tekst hebt van ongekende lengte, die je in je tabel wil steken, in een kolom van type VARCHAR2(255), wat doe je dan als je tekst langer is dan 255 characters?
Hij lost dit nu op door de eerste 255 characters in de tabel te steken en kolom B de waarde "a" te geven, en de volgende 255 characters met waarde "b" in kolom B, etc.
Bijvoorbeeld: ik wil de tekst

Dit is een voorbeeldtekst

in een kolom steken van 10 characters breed, dan wordt dit

A	B	C
1	a	Dit is een
1	b	voorbeeld
1	c	tekst

[Voor 14% gewijzigd door Big Womly op 26-05-2011 14:29]

[BasieP]

Big Womly schreef op donderdag 26 mei 2011 @ 14:23:
[...]

Ik volg je in je samenvatting

Wat hij met kolom A en B bedoeld, is volgens mij dat 1 een soort van ID is, en B een sequentie.
Stel dat je een tekst hebt van ongekende lengte, die je in je tabel wil steken, in een kolom van type VARCHAR2(255), wat doe je dan als je tekst langer is dan 255 characters?
Hij lost dit nu op door de eerste 255 characters in de tabel te steken en kolom B de waarde "a" te geven, en de volgende 255 characters met waarde "b" in kolom B, etc.

wie dat bedenkt verdient een schop.. daarvoor hebben ze text velden uitgevonden..
maargoed ik begreep dat de TS hier weinig aan kon doen.

[Quantor]

Skinny schreef op donderdag 26 mei 2011 @ 13:35:
aan de tabelnaam (dbo.x) te zien gebruik je MSSQL. Volgens mij kun je gewoon doen :

SQL:

SELECT SUM(DISTINCT c), SUM(d), e
FROM pseudo
GROUP BY e

Daarmee krijg ik het resultaat wat jij wil. De DISTINCT zorgt ervoor dat er per GROUP alleen de unieke waarden worden geSUMmed.

zie ook : MSDN: SUM (Transact-SQL)

Ik ga er gemakshalve dus even vanuit dat voor kolom C geldt dat er per combinatie van E/B steeds dezelfde C voorkomt
...

En je gaat er vanuit dat de C uniek is voor elke E combinatie, wat mij niet het geval lijkt te zijn (wel in het voorbeeld tabel) Of zie ik dat nu verkeerd?

[Skinny]

Quantor schreef op donderdag 26 mei 2011 @ 14:36:
[...]


En je gaat er vanuit dat de C uniek is voor elke E combinatie, wat mij niet het geval lijkt te zijn (wel in het voorbeeld tabel) Of zie ik dat nu verkeerd?

Je hebt gelijk.. niet de E/B, maar per E (Type "product") dat dezelfde te verwachten waarde (C) aanwezig is.

Wat meer context zou echter niet overbodig zijn, want die DISTINCT kan ook nog wel eens nasty worden in bepaalde situaties

[Voor 14% gewijzigd door Skinny op 26-05-2011 14:40]

[jmzeeman]

Big Womly schreef op donderdag 26 mei 2011 @ 14:23:
[...]
Wat hij met kolom A en B bedoeld, is volgens mij dat 1 een soort van ID is, en B een sequentie.
[...]

Ik denk dat je eerste observatie goed is maar dat je verdere uitwerking naar multi row tekstvelden een beetje te ver gaat en ook uit niks uit de info van de TS blijkt. Er zijn natuurlijk ladingen situaties waar een id met sequentie wel een logische reden heeft en gewoon een goede modellering is (hoeft in dit geval natuurlijk niet zo te zijn).

Mijn eigen volledig uit de lucht gegrepen interpetatie zou zijn dat a het id van een meet instrument is en b de naam van een component van de meetwaarde.
offtopic:
Mooi voorbeeld waar de door jouw beschreven techniek gebruikt wordt is het beruchte patent van MS op lange namen in FAT

[Voor 8% gewijzigd door jmzeeman op 26-05-2011 14:49]

[Wijnbo]

Blegh.

Bedankt voor de replies. Ik heb er helaas niets aan want heb een fout in mijn beschrijving gemaakt.

Er zit nog een ander situatie in, waardoor bovenstaande queries niet werken. Heb het nu maar opgelost met een functie die alles ophaalt aan de hand van een key. Slechtere performance, maja.

Toch bedankt.

[Skinny]

Eerlijk gezegd ben ik nu wel erg nieuwsgierig geworden naar wat het daadwerkelijk probleem nu voorstelt. Kun je de beschrijving niet corrigeren ?

[Wijnbo]

Skinny schreef op donderdag 26 mei 2011 @ 16:17:
Eerlijk gezegd ben ik nu wel erg nieuwsgierig geworden naar wat het daadwerkelijk probleem nu voorstelt. Kun je de beschrijving niet corrigeren ?

Komt er op neer dat een sum gemaakt moet van een hele bups data waarvan sommige regels toch stiekem bij elkaar horen. Anyways, ga op zoek naar een betere oplossing. Data aanpassen waarschijnlijk, dit schiet niet op zo

[thioz]

@Wijnbo.. lekker duidelijk ... eeh.. NOT !

Maar ja ... misschien toch maar een 2e table bijmaken om die relatie tussen die regels te leggen ?

of gewoon lekker lomp in code het optellen doen, altijd een stuk makkelijker om condities in code op te zetten ipv in een query

[Precision]

Dit topic mag van mij genomineerd worden tot "het meest onduidelijk topic 2011". Ik snap er niets van, noch van het probleem noch van de "oplossing".

[ValHallASW]

Als ik het goed begrijp:

*Het probleem*: je hebt niet-genormaliseerde data. Je hebt

code:

a b c d e

1 a 9 3 1
1 b 9 5 1
2 a 8 7 1
3 a 5 5 2
4 a 7 3 2
4 b 7 4 2

waar je had moeten hebben

code:

a c e

1 9 1
2 8 1
3 5 2
4 7 2

én

code:

a d

1 3
1 5
2 7
3 5
4 3
4 4

Waarbij je je data er triviaal uit kunt vissen.