SQL question.... Trying to simulate DCOUNT 1

[RSfromCO]

I'm having a problem putting this concept into SQL. I'm hoping someone can help....

Let's say I have tables CUSTOMERS and ORDERS.

CUSTOMERS is straight forward, 1 record for each customer info.

ORDERS contains one record for each Order placed. ORDER records contain a CustomerID and an OrderType (integer).

Let's say there are 5 possible OrderTypes(1-5). A customer may have no orders or they could have multiple orders, each ORDER record has an OrderType (a value between 1 and 5).

I want to create a SELECT query that produces a single record for each CUSTOMER. Each output record needs to contain a count of the number of ORDERS of OrderType=1, a count of the number of ORDERS of OrderType=2, a count of the number of ORDERS of OrderType=3, etc. (for that CUSTOMER)

I know how to create this beast in MS-Access using the DCOUNT function, but I am using SQL Server here.

Any suggestions please?

 

[gmmastros]

Does this return the data you're looking for?

[COLOR=blue]Select[/color] Customers.CustomerId,
       Sum([COLOR=blue]Case[/color] [COLOR=blue]When[/color] Orders.OrderType = 1 [COLOR=blue]Then[/color] 1 [COLOR=blue]Else[/color] 0 [COLOR=blue]End[/color]) [COLOR=blue]As[/color] OrderType1,
       Sum([COLOR=blue]Case[/color] [COLOR=blue]When[/color] Orders.OrderType = 2 [COLOR=blue]Then[/color] 1 [COLOR=blue]Else[/color] 0 [COLOR=blue]End[/color]) [COLOR=blue]As[/color] OrderType2,
       Sum([COLOR=blue]Case[/color] [COLOR=blue]When[/color] Orders.OrderType = 3 [COLOR=blue]Then[/color] 1 [COLOR=blue]Else[/color] 0 [COLOR=blue]End[/color]) [COLOR=blue]As[/color] OrderType3,
       Sum([COLOR=blue]Case[/color] [COLOR=blue]When[/color] Orders.OrderType = 4 [COLOR=blue]Then[/color] 1 [COLOR=blue]Else[/color] 0 [COLOR=blue]End[/color]) [COLOR=blue]As[/color] OrderType4,
       Sum([COLOR=blue]Case[/color] [COLOR=blue]When[/color] Orders.OrderType = 5 [COLOR=blue]Then[/color] 1 [COLOR=blue]Else[/color] 0 [COLOR=blue]End[/color]) [COLOR=blue]As[/color] OrderType5
[COLOR=blue]From[/color]   Customers
       [COLOR=#FF00FF]Left[/color] [COLOR=blue]Join[/color] Orders
         [COLOR=blue]On[/color] Customers.CustomerId = Orders.CustomerId
[COLOR=blue]Group[/color] [COLOR=blue]By[/color] Customers.CustomerId

If this works, and you'd like me to explain any part of it, just let me know.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[PatriciaObreja]

Select Customers.CustomerId,
(Select count(*) from Orders where Orders.CustomerId=Customers.CustomerId and Orders.OrderType = 1) AS OrderType1,
(Select count(*) from Orders where Orders.CustomerId=Customers.CustomerId and Orders.OrderType = 2) AS OrderType2,
(Select count(*) from Orders where Orders.CustomerId=Customers.CustomerId and Orders.OrderType = 3) AS OrderType3,
(Select count(*) from Orders where Orders.CustomerId=Customers.CustomerId and Orders.OrderType = 4) AS OrderType4,
(Select count(*) from Orders where Orders.CustomerId=Customers.CustomerId and Orders.OrderType = 5) AS OrderType5
FROM Customers

-------------------------------------------------------------------------------------------------------------------------
"Now I can look at you in peace; I don't eat you any more." Franz Kafka, while admiring fish in an aquarium

 

[RSfromCO]

gmmastros and Patricia, your suggestions work great and do exactly what I wanted. Now I need to try something similar but with a twist.

Now if I add a 3rd table, a lookup table (LU_OrderTypes) that defines what OrderTypes there are.... OrderTypeID and Description fields. Something like...

LU_OrderTypes
1 = "Internet Order"
2 = "Monthly Order"
3 = "Phone Order"
4 = "Counter Order"
5 = "Mail Order"

NOW I need a query similar to the first, but instead of telling me how many of each type of orders there were, list in a single string the TYPES of orders that a customer used (no mention of HOW MANY of each OrderType). (and for good measure put a semi-colon between each OrderType used.

Such as....
Joe Smith, Monthly Order; Counter Order
John Doe, Internet Order
Jimmy Johnson, Phone Order; Mail Order
Sally Blue, Internet Order; Phone Order; Mail Order

Any suggestions?

 

[gmmastros]

Do you understand how my query works? If you truly understood it, then you should be able to 'tweak' it for your new query.

I'll give you some hints.

Using my original query as a basis for this, I would suggest that you LEFT JOIN to the LU_OrderTypes table. Then, change the select clause to return the Max of the 'description' column.

[tt][blue]Max(Case When LU_OrderTypes.OrderType = 1 Then LU_OrderTypes.Description Else NULL END)[/blue][/tt]


This, of course, won't get you a semi-colon delimited string of values, but if you can get this far, then you've made a lot of progress. I encourage you to 'try it'. If you get stuck, then show us the query you have so far and we'll help more.


-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[PatriciaObreja]

This query will give you in the first column the "distinct" description for the order type for a specific customer, and in the second column the CustomerId:

Select DISTINCT(OrdType.Description), Customers.CustomerId
From Customers
Left Join Orders
ON Customers.CustomerId = Orders.CustomerId
Left Join OrdType
ON Orders.OrderType = OrdType.OrderType
GROUP BY Customers.CustId, OrdType.Descr

-------------------------------------------------------------------------------------------------------------------------
"Now I can look at you in peace; I don't eat you any more." Franz Kafka, while admiring fish in an aquarium

 

[RSfromCO]

Thanks for the response George. I do understand how your query works. I had tried something similar myself, but I was trying to use something like FIRST rather than MAX. After reviewing your suggestion, and making a quick adjustment its working great. I'm not sure the best way to get a single string out of all of the individual fields, so you might glance at my code and see if what I did is reasonable (its working). I defined the whole select as a sub-query (A). I had to modify the CASE statement to put in an empty string rather than NULL, otherwise my concatination was always returning NULL if any of the fields were NULL.

SELECT A.*, A.Text_1 + A.Text_2 + A.Text_3 + A.Text_4 + A.Text_5 AS ConcatText
FROM
(SELECT dbo.Customers.ID AS CustomerID, 
MAX(CASE WHEN dbo.LU_OrderTypes.ID = 1 THEN dbo.LU_OrderTypes.OrderType + '; ' ELSE '' END) AS Text_1, 
MAX(CASE WHEN dbo.LU_OrderTypes.ID = 2 THEN dbo.LU_OrderTypes.OrderType + '; ' ELSE '' END) AS Text_2, 
MAX(CASE WHEN dbo.LU_OrderTypes.ID = 3 THEN dbo.LU_OrderTypes.OrderType + '; ' ELSE '' END) AS Text_3, 
MAX(CASE WHEN dbo.LU_OrderTypes.ID = 4 THEN dbo.LU_OrderTypes.OrderType + '; ' ELSE '' END) AS Text_4, 
MAX(CASE WHEN dbo.LU_OrderTypes.ID = 5 THEN dbo.LU_OrderTypes.OrderType + '; ' ELSE '' END) AS Text_5 

FROM 
dbo.LU_OrderTypes RIGHT OUTER JOIN
dbo.Orders ON 
dbo.LU_OrderTypes.ID = dbo.Orders.OrderTypeID RIGHT OUTER JOIN
dbo.Customers ON dbo.Orders.CustomerID = dbo.Customers.ID

GROUP BY dbo.Customers.ID) A

 

[gmmastros]

Just some terminology... What you did is called a derived table, not a sub-query. There's a subtle difference between the them.

Your query looks a lot like that query that I would have used. I'm very glad that you were able to figure this out for yourself. You probably learned more in the process and will be able to effectively apply this to other queries. Good Work!

Now... for the query... I don't think you need to use a derived table. You can just concatenate the string as you go. Like this...

SELECT dbo.Customers.ID AS CustomerID,
       MAX(CASE WHEN dbo.LU_OrderTypes.ID = 1 THEN dbo.LU_OrderTypes.OrderType + '; ' ELSE '' END)
       + MAX(CASE WHEN dbo.LU_OrderTypes.ID = 2 THEN dbo.LU_OrderTypes.OrderType + '; ' ELSE '' END)
       + MAX(CASE WHEN dbo.LU_OrderTypes.ID = 3 THEN dbo.LU_OrderTypes.OrderType + '; ' ELSE '' END)
       + MAX(CASE WHEN dbo.LU_OrderTypes.ID = 4 THEN dbo.LU_OrderTypes.OrderType + '; ' ELSE '' END)
       + MAX(CASE WHEN dbo.LU_OrderTypes.ID = 5 THEN dbo.LU_OrderTypes.OrderType + '; ' ELSE '' END) AS ConcatText
FROM   dbo.LU_OrderTypes RIGHT OUTER JOIN
       dbo.Orders ON
       dbo.LU_OrderTypes.ID = dbo.Orders.OrderTypeID RIGHT OUTER JOIN
       dbo.Customers ON dbo.Orders.CustomerID = dbo.Customers.ID
GROUP BY dbo.Customers.ID

Derived tables are a very powerful tool for constructing queries. Like anything else, though... they should only be used when necessary. So, if you can write the query without the derived table, it will be better (for performance) if you leave it out.

Make sense?

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[RSfromCO]

All I can say is WOW! Yes it makes sense. Thanks for sharing your expertise George!