April 27, 2004 at 8:57 pm
I have the following complex query ...
I want totals for each Merchant
such as for PointsIssued and PointsRedeemed
But when I run the query I only get one total which is the same for all merchants. This isn't right. Am I missing anything with my sum functions or is my groupings wrong.
Any help is welcomed.
SELECT DISTINCT Merchant.MerchantId, Merchant.MerchantName, DefinedPaymentDistribution.DistributionName, PaymentType.PaymentTypeName,
...
SUM(Transactions.TotalPointsIssuedCount) AS TotalPointsIssued, SUM(Transactions.TotalPointsRedeemedCount) AS TotalPointsRedeemed FROM DirectDebitEntry INNER JOIN Merchant ON ...
WHERE (DirectDebitEntry.DirectDebitId = 1) AND (Transactions.DailyProcessedCalculationId = Calculation.CalculationId) OR (DirectDebitEntry.DirectDebitId = 1) AND (Transactions.MonthlyProcessedCalculationId = Calculation.CalculationId) GROUP BY Merchant.MerchantId, Merchant.MerchantName, DefinedPaymentDistribution.DistributionName, PaymentType.PaymentTypeName, Payment.PaymentAmount, Payment.GSTAmount, DirectDebitEntry.DirectDebitEntryId, CONVERT(varchar(10), DirectDebit.DirectDebitDate, 103), CONVERT(varchar(8), DirectDebit.DirectDebitTime, 114) ORDER BY DirectDebitEntry.DirectDebitEntryId
Thanks Brian
You are never an expert, you are always learning!April 27, 2004 at 11:16 pm
I think you are summing across the entire resultset – so you will get the same numbers every time. You need something more along these lines:
SELECT Merchant.MerchantId, Merchant.MerchantName, DefinedPaymentDistribution.DistributionName, PaymentType.PaymentTypeName,
(remove 'DISTINCT' keyword, leave the summed columns as before)
and, after the WHERE clause, add a GROUP BY clause:
GROUP BY Merchant.MerchantId, Merchant.MerchantName, DefinedPaymentDistribution.DistributionName, PaymentType.PaymentTypeName
This should get you moving in the right direction. Remember that all of your non-summed SELECT columns will need to appear in the GROUP BY clause.
Good luck.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply