Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
Apples
Posting Yak Master
146 Posts |
 Posted - 2008-06-11 : 11:25:27
|
| Here's my tables:-------------------------------------------------------tblMembers-------------------------------------------------------MemberID | CountryID--------------------------------------------------------------------------------------------------------------tblCountries-------------------------------------------------------CountryID | CountryName--------------------------------------------------------------------------------------------------------------tblOrders-------------------------------------------------------OrderID | MemberID | OrderTypeID--------------------------------------------------------------------------------------------------------------tblSubscriptionOrders-------------------------------------------------------SubscriptionOrderID | OrderID | SubscriptionPackID--------------------------------------------------------------------------------------------------------------tblSubscriptionPacks-------------------------------------------------------SubscriptionPackID | TypeID-------------------------------------------------------Here's what I'm trying to do: 1. Output each country in one column 2. Output the number of subscriptions made from a member of that country where tblOrders.orderTypeID = 3 and tblSubscriptionPacks.TypeID = 1 in the next column 3. Output the number of subscriptions made from a member of that country where tblOrders.orderTypeID = 3 and tblSubscriptionPacks.TypeID = 2 in the next columnMy problem was that I was doing joins, and I was somehow ending up with orders where the OrderTypeID was NOT equal to 3, even though I declared it specifically in the WHERE clause.Can someone help me with this query? |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-06-11 : 11:33:09
|
| [code]SELECT c.CountryName,SUM(CASE WHEN sp.TypeID=1 THEN 1 ELSE 0 END) AS Type1Count,SUM(CASE WHEN sp.TypeID=2 THEN 1 ELSE 0 END) AS Type2CountFROM tblOrders oINNER JOIN tblMembers mON m.MemberID=o.MemberIDAND o.OrderTypeID=3INNER JOIN tblCountries cON c.CountryID=m.CountryIDINNER JOIN tblSubscriptionOrders soON so.OrderID=o.OrderIDINNER JOIN tblSubscriptionPacks spON sp.SubscriptionPackID=so.SubscriptionPackIDGROUP BY c.CountryName[/code]If this is not what you expected, post some sample data and show us what output you want out of them |
 |
|
|
Apples
Posting Yak Master
146 Posts |
Posted - 2008-06-11 : 11:44:24
|
| That looks good, except that's not counting orders where OrderTypeID = 3. It's also counting the other orders that a member has made if the member has made an order where the OrderTypeID = 3.That's the same problem I was having. |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-06-11 : 11:48:49
|
quote:
Originally posted by Apples
That looks good, except that's not counting orders where OrderTypeID = 3. It's also counting the other orders that a member has made if the member has made an order where the OrderTypeID = 3.That's the same problem I was having.
Can you provide some sample data and illustrate?b/w i had a small mistake in earlier query. It should have been GROUP BY not ORDER BY |
|
|
|
Apples
Posting Yak Master
146 Posts |
Posted - 2008-06-11 : 12:12:23
|
quote:
Originally posted by visakh16
quote:
Originally posted by Apples
That looks good, except that's not counting orders where OrderTypeID = 3. It's also counting the other orders that a member has made if the member has made an order where the OrderTypeID = 3.That's the same problem I was having.
Can you provide some sample data and illustrate?b/w i had a small mistake in earlier query. It should have been GROUP BY not ORDER BY
Actually, now that I look closer at the data, what you're doing is right. Your query works perfectly, thank you. |
|
|
|
|
|
|
|
|
Help with SELECT query with COUNT