Southern_Gentleman
asked on
Total Column to sum row
I have a simple SQL Server cross tab select query. I'm trying to simply add a column at the far right named Total to add up the 7 column rows to the left but it keeps saying that its an invalid column name. I was trying to
Sum(A+B+C+D+E+F+G) AS Total
Sum(A+B+C+D+E+F+G) AS Total
SELECT ProductsDetails.Gender, ProductsDetails.CorePN, ProductsDetails.productdescription, ProductsDetails.Color, ProductsDetails.Category,
SUM(CASE WHEN ProductsDetails.SizeNo = 1 THEN Products.stockstatus ELSE 0 END) AS A,
SUM(CASE WHEN ProductsDetails.SizeNo = 2 THEN Products.stockstatus ELSE 0 END) AS B,
SUM(CASE WHEN ProductsDetails.SizeNo = 3 THEN Products.stockstatus ELSE 0 END) AS C,
SUM(CASE WHEN ProductsDetails.SizeNo = 4 THEN Products.stockstatus ELSE 0 END) AS D,
SUM(CASE WHEN ProductsDetails.SizeNo = 5 THEN Products.stockstatus ELSE 0 END) AS E,
SUM(CASE WHEN ProductsDetails.SizeNo = 6 THEN Products.stockstatus ELSE 0 END) AS F,
SUM(CASE WHEN ProductsDetails.SizeNo = 7 THEN Products.stockstatus ELSE 0 END) AS G
FROM Products INNER JOIN ProductsDetails ON Products.productcode = ProductsDetails.productcode
GROUP BY ProductsDetails.Site, ProductsDetails.Gender, ProductsDetails.CorePN, ProductsDetails.productdescription, ProductsDetails.Color, ProductsDetails.Category
HAVING (ProductsDetails.Site LIKE N'Event Stock')
ORDER BY ProductsDetails.Gender, ProductsDetails.CorePN, ProductsDetails.Category, ProductsDetails.Color
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
If you are using SQL Server 2005 and up then you can resort to using a CTE or a PIVOT.
ASKER
Thanks a bunch Anthony, I usually do alias for the tables but figure I give a little more description. Thanks again for the extra info and other ways to go about this.
Open in new window
Notice the way, that I am using aliases, this makes it easier to read. Also, notice that I placed your condition in the WHERE clause instead of the HAVING. Typically you use LIKE with wildcards so instead of say d.Site LIKE N'Event Stock', it could be d.Site LIKE N'Event Stock%'. If you do want an exact match then I would write it as d.Site = N'Event Stock' and lose the d.Site in the GROUP BY clause