| Author |
Topic |
|
timgaunt
Posting Yak Master
115 Posts |
 Posted - 2007-09-17 : 09:04:32
|
| I'm sure I'm just not thinking today, but I have the following three tables (simplified):Order----------OrderIdOrderNameRecipient----------RecipientIdOrderIdRecipientNameOrderItem----------RecipientIdPriceWhat I would like to do is produce a table including:OrderIdOrderNameTotalRecipient (COUNT of Recipient.RecipientId)OrderTotal (SUM of OrderItem.Price)But the only way I can think of doing this without having a silly number of recipients is with multiple selects (or a view) within the same statement, is there a better way of doing this?TIATim----------------------------I've finally started blogging (all be it badly!)Check it out:http://blogs.thesitedoctor.co.uk/tim/ |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2007-09-17 : 09:51:11
|
[code]SELECT o.OrderId, o.OrderName, COUNT(r.OrderId) AS TotalRecipient, SUM(ISNULL(oi.Price, 0)) AS OrderTotalFROM Order AS oLEFT JOIN Recipient AS r ON r.OrderId = o.OrderIdLEFT JOIN OrderItem AS oi ON oi.RecipientId = r.RecipientId[/code]
E 12°55'05.25"N 56°04'39.16" |
 |
|
|
jimf
Master Smack Fu Yak Hacker
2875 Posts |
Posted - 2007-09-17 : 09:51:33
|
| This is a start, but you will still be Cartesian since there is no way to uniquely link a recipientid to an orderid. You would be okay without grouping on the orderid, or by adding orderid to recipient tableSELECT o.orderid ,o.ordername ,a.TotalRecipient ,b.OrderTotalFROM [order] oINNER JOIN (select orderid,recipentid,'TotalRecipent' = count(recipientid) from recipient group by orderid,recipentid) aON o.orderid = a.orderidINNER JOIN (select recipientID,'OrderTotal' = sum(Price) from OrderItem group by recipientID) bON a.recipientid = b.recipientid Jim |
|
|
|
timgaunt
Posting Yak Master
115 Posts |
Posted - 2007-09-17 : 10:12:48
|
Peso thanks, I should have included my query because that's what I was doing, the issue being if there are multiple items per recipient (which there will be) it increases the countjimf -thanks, I've also done something along those lines however it seemed a little OTT having 3 selects for 1...quote:
there is no way to uniquely link a recipientid to an orderid
I take it you mean OrderItem not Recipient as OrderId is in the Recipient table. I decided not to include it in the OrderItem table is it felt like I was duplicating data...Tim----------------------------I've finally started blogging (all be it badly!)Check it out:http://blogs.thesitedoctor.co.uk/tim/ |
|
|
|
jsmith8858
Dr. Cross Join
7423 Posts |
|
|
timgaunt
Posting Yak Master
115 Posts |
Posted - 2007-09-17 : 11:34:57
|
| Exactly what I was after ta, COUNT(DISTINCT...) did the trick.Tim----------------------------I've finally started blogging (all be it badly!)Check it out:http://blogs.thesitedoctor.co.uk/tim/ |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2007-09-17 : 11:36:09
|
The article is all about NOT using "COUNT DISTINCT"...
E 12°55'05.25"N 56°04'39.16" |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2007-09-17 : 11:50:32
|
Also, how are you going to distinguish between several same RecepientID between orders?
E 12°55'05.25"N 56°04'39.16" |
|
|
|
itzmohikumar
Starting Member
3 Posts |
Posted - 2007-09-20 : 04:29:25
|
| Is it the same case when we are taking SUM from different child tables with one-many relationship?Will this not effect the performance of the query exceution if we have approximately 1 million record in each table?Is there any other alternate solution for this?Can anybody please, help me on sorting out this problem.Thanks in advance |
|
|
|
jsmith8858
Dr. Cross Join
7423 Posts |
|
|
|
SUM and COUNT with different tables