May 9, 2007 at 3:34 pm
I am trying to write a query to analyze data with an unusual (for me) grouping problem.
I have Customers (CustID) which purchase items (plumbing, lumber, flooring - PurchaseType) from different stores in the area. I need to group the number of a particular purchase made by a cust. per store per month and quarter and if their payments were late (IsLate). For instance:
CustID PurchaseType TotalCt Store Month Qtr
------ -------------- ------- ----- ------ ----
Cust01 Flooring 5 A 1 1
Cust01 Lumber 1 A 1 1
Cust01 Lumber 3 B 1 1
Cust02 Plumbing 1 B 1 1
So this is my query:
SELECT CustID, PurchaseType, Store, COUNT(*) AS TotalCt
DATEPART(mm,PurDate) AS Month, datepart(qq,PurDate) AS Qtrr
FROM dbo.HomeImprovement
WHERE IsLate = N'Y'
GROUP BY CustID, PurchaseType, Store, PurDate
ORDER BY Qtr, Month
The data is returning:
CustID PurchaseType TotalCt Store Month Qtr
------ -------------- ------- ----- ------ ----
Cust01 Flooring 2 A 1 1
Cust01 Flooring 3 A 1 1
Cust01 Lumber 1 A 1 1
Cust01 Lumber 2 B 1 1
Cust01 Lumber 1 B 1 1
Cust02 Plumbing 1 B 1 1
I know I am missing something really fundamental here (have not had my "aa haa" moment yet).
Argue for your limitations, and sure enough they're yours (Richard Bach, Illusions)
May 9, 2007 at 3:55 pm
Actually,
Your probably getting the issue due to the PurDate Not knowing what datatype it is.
Try
SELECT CustID, PurchaseType, Store, COUNT(*) AS TotalCt
DATEPART(mm,PurDate) AS Month, datepart(qq,PurDate) AS Qtrr
FROM dbo.HomeImprovement
WHERE IsLate = N'Y'
GROUP BY CustID, PurchaseType, Store, DATEPART(mm,PurDate) ,
datepart(qq,PurDate)
May 10, 2007 at 11:42 am
Ray... THANK YOU, THANK YOU, THANK YOU!
That appears to have solved the problem. I was not aware that you could put nonaggregate expressions in a group by, but I did know that a column alias that is defined in the select list cannot be used.
Argue for your limitations, and sure enough they're yours (Richard Bach, Illusions)
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply