[SQL] Query met distinct en som van velden

[DukeMan]

Ik probeer enkele gegevens uit een order tabel te halen. In deze tabel staan alle verschillende artikelen die bij een order horen.

Om diverse redenen staat, indien een artikel 2 keer of meer gekocht is, deze ook 2 keer of vaker in de tabel. Helaas hier kan ik niets aan veranderen .

Nu wil ik een query maken die uit deze tabel alle artikelen behorende bij een bepaalde order haalt, en hiervan enkele gegevens opvragen. Het liefst wil ik dan ook dat ik de query dubbele eruit filter. Dit kan middels DISTINCT. Dit lukt mij ook wel alleen wil ik iets extra's waarbij ik niet weet hoe ik dat voor elkaar kan krijgen...

Hierbij eerste de query zoals ik die gebruik:

code:

SELECT DISTINCT 
             tbdOrderItem.fldItemName, tbdOrderItem.fldItemCode, 
             tbdOrderItem.fldAmount, tbdOrderItem.fldWebDeliverd, 
             tbdOrderItem.fldTax, tbdOrderItem.fldTaxID, 
             tbdOrderItem.fldTaxAmount, tbdStatus.fldStatus, 
             tbdStatus.fldID AS fldStatusID
FROM    tbdOrderItem INNER JOIN
             tbdStatus ON tbdOrderItem.fldWebStatus = tbdStatus.fldID
WHERE tbdOrderItem.fldOrder = '<orderID>'
ORDER BY tbdOrderItem.fldItemCode

<orderID> wordt vervangen door ID order.

Wanneer ik deze query run krijg ik netjes alle artikelen terug. Indien er dubbele artikelen zijn die verder dezelfde gegevens hebben (=Status ed.) staan deze artikelen ook maar 1 keer in de results. Indien bv. de status anders is krijg ik het artikel 2 keer.

Nu wil ik graag weten of het mogelijk is om, indien er een DISTINCT gedaan wordt, of ik enkele velden dan bij elkaar kan optellen. Een voorbeeld hiervan is fldAmount. Nu krijg ik namelijk maar 1 keer het artikel te zien, terwijl de klant het artikel misschien 2 keer gekocht heeft. Kan ik het fldAmount veld dan de beide records op laten tellen, zodat ik 2 keer het fldAmount te zien krijg???

Ik kan op zich die DISTINCT wel weghalen en dan in mijn eigen code op dubbele zoeken, maar dit is misschien wel een stuk sneller?!?

Ik kon hier niets van gevonden krijgen, maar misschien zoek ik met de verkeerde zoektermen.

edit:

de applicatie waarin ik dit maak is VB6.

[ Voor 3% gewijzigd door DukeMan op 13-01-2004 22:37 ]

[Batsies]

sum(fldTaxAmount) of zie ik het verkeerd, dan wel ff group by amount.. toch

[DukeMan]

Batsies schreef op 13 januari 2004 @ 22:44:
sum(fldTaxAmount) of zie ik het verkeerd, dan wel ff group by amount.. toch

Hmm... dit werkt ook niet...
Waneer ik het volgende doe

code:

SELECT DISTINCT 
             tbdOrderItem.fldItemName, tbdOrderItem.fldItemCode, 
             SUM(tbdOrderItem.fldAmount) AS fldAmount, 
             tbdOrderItem.fldWebDeliverd, tbdOrderItem.fldTax, 
             tbdOrderItem.fldTaxID, tbdOrderItem.fldTaxAmount,  
             tbdStatus.fldStatus, tbdStatus.fldID AS fldStatusID
FROM    tbdOrderItem INNER JOIN
             tbdStatus ON tbdOrderItem.fldWebStatus = tbdStatus.fldID
WHERE  tbdOrderItem.fldTransaction = '<orderID>'
ORDER BY fldAmount

Krijg ik de volgende foutmelding:

Column 'tbdTransactionItem.fldItemName' is invalid in the select list because it is not contained in an aggregate function and there is no ORDER By clause

[BrZ]

Het moet iig met een GROUP BY, en ook met een subquery om overige gegevens op te vragen.
Was even aan het kijken hoe die query eruit gaat zien, maar daarvoor zou je iets maar over de structuur van je tabellen moeten vertellen, vind het eerlijk gezegd een beetje vaag zo

[ProgrammerX]

NTGiant schreef op 13 januari 2004 @ 22:53:
[...]

Hmm... dit werkt ook niet...
Waneer ik het volgende doe

code:

SELECT DISTINCT 
             tbdOrderItem.fldItemName, tbdOrderItem.fldItemCode, 
             SUM(tbdOrderItem.fldAmount) AS fldAmount, 
             tbdOrderItem.fldWebDeliverd, tbdOrderItem.fldTax, 
             tbdOrderItem.fldTaxID, tbdOrderItem.fldTaxAmount,  
             tbdStatus.fldStatus, tbdStatus.fldID AS fldStatusID
FROM    tbdOrderItem INNER JOIN
             tbdStatus ON tbdOrderItem.fldWebStatus = tbdStatus.fldID
WHERE  tbdOrderItem.fldTransaction = '<orderID>'
ORDER BY fldAmount

Krijg ik de volgende foutmelding:

Column 'tbdTransactionItem.fldItemName' is invalid in the select list because it is not contained in an aggregate function and there is no ORDER By clause

Het kan natuurlijk zijn dat ik het helemaal verkeerd zie nu het zo laat is, maar ik zie geen 'tbdTransactionItem.fldItemName' staan in de bovenstaande query dus ik vind die melding inderdaad wel erg vaag

[DukeMan]

ProgrammerX schreef op 14 januari 2004 @ 02:01:
[...]


Het kan natuurlijk zijn dat ik het helemaal verkeerd zie nu het zo laat is, maar ik zie geen 'tbdTransactionItem.fldItemName' staan in de bovenstaande query dus ik vind die melding inderdaad wel erg vaag

De tabel heet eigenlijk tbdTransactionItem maar had hem hier even tbdOrderItem genoemd (was iets korter. )

BrZ schreef op 13 januari 2004 @ 23:52:
Het moet iig met een GROUP BY, en ook met een subquery om overige gegevens op te vragen.
Was even aan het kijken hoe die query eruit gaat zien, maar daarvoor zou je iets maar over de structuur van je tabellen moeten vertellen, vind het eerlijk gezegd een beetje vaag zo

De tabel tbdTransactionItem (dus tbdOrderItem) ziet er als volgt uit:

code:

CREATE TABLE [dbo].[tbdTransactionItem] (
    [fldID] [uniqueidentifier] NOT NULL ,
    [fldTransaction] [uniqueidentifier] NOT NULL ,
    [fldType] [int] NULL ,
    [fldItemID] [uniqueidentifier] NULL ,
    [fldItemName] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [fldItemCode] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [fldDefaultAmount] [varchar] (8) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [fldAmountNoReduction] [varchar] (8) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [fldAmount] [varchar] (8) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [fldNumber] [varchar] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [fldTotal] [varchar] (8) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [fldTax] [smallint] NULL ,
    [fldTaxAmount] [int] NULL ,
    [fldTaxID] [uniqueidentifier] NULL ,
    [fldReduction] [bit] NULL ,
    [fldReductionAmount] [varchar] (6) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [fldReductionProcent] [varchar] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [fldOrder] [int] NULL ,
    [fldRestoredTransactionID] [uniqueidentifier] NULL ,
    [fldWebArticleNotDeliverd] [bit] NULL ,
    [fldWebDeliveryReason] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [fldWebStatus] [uniqueidentifier] NULL ,
    [fldWebDeliveryDate] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [fldWebRestoredArticle] [bit] NULL ,
    [fldWebRestoredReason] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [fldWebWeight] [int] NULL ,
    [fldWebDeliverd] [bit] NULL 
)

En tbdStatus:

code:

CREATE TABLE [dbo].[tbdStatus] (
    [fldID] [uniqueidentifier] NOT NULL ,
    [fldType] [smallint] NOT NULL ,
    [fldStatus] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [fldOrder] [int] NULL ,
    [fldColor] [varchar] (6) COLLATE SQL_Latin1_General_CP1_CI_AS NULL 
)

Misschien dat je hier meer mee kunt...

[whoami]

Hmm, dit is gewoon basic SQL hoor. Diep even een SQL tutorial op, en kijk eens naar de aggregated functions. Daar zal ook staan dat je de GROUP BY clausule moet gebruiken:

code:

select veld1, veld2, sum(veld3)
from tabel
group by veld1, veld2

[Verwijderd]

Zoek in de SQL Server help even naar GROUP BY. Daar kun je vinden hoe je dit probleem op moet lossen.

[Riegstar]

Dan krijg je zoiets als:

code:

SELECT
             tbdOrderItem.fldItemName, tbdOrderItem.fldItemCode, 
             SUM(DISTINCT tbdOrderItem.fldAmount) AS fldAmount, 
             tbdOrderItem.fldWebDeliverd, tbdOrderItem.fldTax, 
             tbdOrderItem.fldTaxID, tbdOrderItem.fldTaxAmount,  
             tbdStatus.fldStatus, tbdStatus.fldID AS fldStatusID
FROM    tbdOrderItem
INNER JOIN tbdStatus ON tbdOrderItem.fldWebStatus = tbdStatus.fldID
WHERE  tbdOrderItem.fldTransaction = '<orderID>'
GROUP BY tbdOrderItem.fldItemName, tbdOrderItem.fldItemCode,
     tbdOrderItem.fldWebDeliverd, tbdOrderItem.fldTax,
     tbdOrderItem.fldTaxID, tbdOrderItem.fldTaxAmount,
     tbdStatus.fldStatus, tbdStatus.fldID
ORDER BY fldAmount

De velden die je in de Select gebruikt moet je in iedergeval in de GroupBy vermelden.

In de Online-help van SQL Servers staat het volgende over SUM:

code:

SUM
Returns the sum of all the values, or only the DISTINCT values, in the expression. 
SUM can be used with numeric columns only. Null values are ignored.

Syntax:
SUM ( [ ALL | DISTINCT ] expression ) 

Arguments:
ALL

Applies the aggregate function to all values. ALL is the default.

DISTINCT

Specifies that SUM return the sum of unique values.

expression

Is a constant, column, or function, and any combination of arithmetic, bitwise,
and string operators. expression is an expression of the exact numeric or
approximate numeric data type category, except for the bit data type.
Aggregate functions and subqueries are not permitted.

Ik wil er direct bij zeggen dat ik dit niet getest heb.

[DukeMan]

Ik heb nu de volgende query, en die werkt perfect:

code:

SELECT tbdOrderItem.fldItemName, tbdOrderItem.fldItemCode, 
            SUM(tbdOrderItem.fldAmount) AS fldAmount, 
            tbdOrderItem.fldWebDeliverd, tbdOrderItem.fldTax, 
            tbdOrderItem.fldTaxID, SUM(tbdOrderItem.fldTaxAmount) AS fldTaxAmount, 
            tbdStatus.fldStatus, tbdStatus.fldID AS fldStatusID
FROM   tbdOrderItem INNER JOIN
            tbdStatus ON tbdOrderItem.fldWebStatus = tbdStatus.fldID
WHERE tbdOrderItem.fldTransaction = '<orderID>'
GROUP BY tbdOrderItem.fldItemName, tbdOrderItem.fldItemCode,  
                 tbdOrderItem.fldWebDeliverd, tbdOrderItem.fldTax, 
                 tbdOrderItem.fldTaxID, tbdOrderItem.fldTaxAmount, 
                 tbdStatus.fldStatus, tbdStatus.fldID
ORDER BY tbdOrderItem.fldAmount

(ik heb alleen de distinct uit de sum gehaald)!

Allemaal bedankt voor de hulp..

[Riegstar]

NTGiant schreef op 14 januari 2004 @ 10:15:
Ik heb nu de volgende query, en die werkt perfect:

code:

SELECT tbdOrderItem.fldItemName, tbdOrderItem.fldItemCode, 
            SUM(tbdOrderItem.fldAmount) AS fldAmount, 
            tbdOrderItem.fldWebDeliverd, tbdOrderItem.fldTax, 
            tbdOrderItem.fldTaxID, SUM(tbdOrderItem.fldTaxAmount) AS fldTaxAmount, 
            tbdStatus.fldStatus, tbdStatus.fldID AS fldStatusID
FROM   tbdOrderItem INNER JOIN
            tbdStatus ON tbdOrderItem.fldWebStatus = tbdStatus.fldID
WHERE tbdOrderItem.fldTransaction = '<orderID>'
GROUP BY tbdOrderItem.fldItemName, tbdOrderItem.fldItemCode,  
                 tbdOrderItem.fldWebDeliverd, tbdOrderItem.fldTax, 
                 tbdOrderItem.fldTaxID, tbdOrderItem.fldTaxAmount, 
                 tbdStatus.fldStatus, tbdStatus.fldID
ORDER BY tbdOrderItem.fldAmount

(ik heb alleen de distinct uit de sum gehaald)!

Allemaal bedankt voor de hulp..

Je hebt gelijk... ben een beetje warrig vandaag..
Graag gedaan.

[VisionMaster]

Tis ook erg verwarrend wat Distinct doet. Tenopzichte van Group By is het een verwijdering van een record, niet een samenvoeging.

[whoami]

DISTINCT filtert niet-unieke rijen uit de resultset.
Een GROUP BY moet je enkel gebruiken icm aggregaatsfuncties.

[Riegstar]

Ja, als bv. Amount de waarden 10,10,5 en 5 heeft.
Dan:
SUM(Amount) = 30
SUM(DISTINCT Amount) = 15

[DukeMan]

Als ik inderdaad een beetje 'logisch' had nagedacht had ik kunnen weten dat het met group by en sum moest werken, maar als je al even naar een query aan het staren bent, zie je door de bomen het boos niet meer