Trying to SUM on an alias field
-
The query I am working on should show whether or not a different SHIP_TO state was used or if the customer's default address was used. It's a little garbled as the DB is complex. My base query works but what I would like in the end is to get total sales by STATE shipped to, using the SUM function. Unfortunately, GROUP BY will not let me group on an alias.
Here's the base query:
SELECT RECEIVABLE.TOTAL_AMOUNT, COUNT_STATE =
CASE
WHEN CUST_ADDRESS.STATE IS NULL THEN CUSTOMER.STATE
ELSE CUST_ADDRESS.STATE
END
FROM SHIPPER_LINE LEFT OUTER JOIN
SHIPPER ON SHIPPER_LINE.PACKLIST_ID = SHIPPER.PACKLIST_ID RIGHT OUTER JOIN
RECEIVABLE_LINE ON SHIPPER_LINE.PACKLIST_ID = RECEIVABLE_LINE.PACKLIST_ID AND
SHIPPER_LINE.LINE_NO = RECEIVABLE_LINE.PACKLIST_LINE_NO RIGHT OUTER JOIN
RECEIVABLE ON RECEIVABLE_LINE.INVOICE_ID = RECEIVABLE.INVOICE_ID RIGHT OUTER JOIN
CUSTOMER ON RECEIVABLE.CUSTOMER_ID = CUSTOMER.ID LEFT OUTER JOIN
CUST_ADDRESS ON CUSTOMER.ID = CUST_ADDRESS.CUSTOMER_ID AND SHIPPER.SHIP_TO_ADDR_NO = CUST_ADDRESS.ADDR_NO
WHERE (RECEIVABLE.INVOICE_DATE >= CONVERT(DATETIME, '2004-01-01 00:00:00', 102) AND RECEIVABLE.INVOICE_DATE <= CONVERT(DATETIME,
'2004-08-01 00:00:00', 102))
I would like to SUM RECEIVABLE.TOTAL_AMOUNT and GROUP BY COUNT_STATE. I found a snip saying to use a sub query but am not sure how to apply this advice.
Your comments and thoughts are appreciated.
Thanks,
Kindest Regards,Sean Wyatt
seanwyatt.com -
I think the point is as simple as :
select ISNULL(CUST_ADDRESS.STATE, CUSTOMER.STATE), sum(RECEIVABLE.TOTAL_AMOUNT)
from ...
group by ISNULL(CUST_ADDRESS.STATE, CUSTOMER.STATE)
???
-
Yep. All of the expressions not used in aggregate functions need to be in the group by clause.
-
Option 1
Use derived table. Just enclose your main query in parenthesis and use it in a FROM clause of the GROUP BY query. You MUST specify alias for derived table.
SELECT COUNT_STATE,
SUM(TOTAL_AMOUNT)
FROM (SELECT RECEIVABLE.TOTAL_AMOUNT,
COUNT_STATE = ISNULL(CUST_ADDRESS.STATE, CUSTOMER.STATE)
FROM SHIPPER_LINE LEFT OUTER JOIN
SHIPPER ON SHIPPER_LINE.PACKLIST_ID = SHIPPER.PACKLIST_ID RIGHT OUTER JOIN
RECEIVABLE_LINE ON SHIPPER_LINE.PACKLIST_ID = RECEIVABLE_LINE.PACKLIST_ID AND
SHIPPER_LINE.LINE_NO = RECEIVABLE_LINE.PACKLIST_LINE_NO RIGHT OUTER JOIN
RECEIVABLE ON RECEIVABLE_LINE.INVOICE_ID = RECEIVABLE.INVOICE_ID RIGHT OUTER JOIN
CUSTOMER ON RECEIVABLE.CUSTOMER_ID = CUSTOMER.ID LEFT OUTER JOIN
CUST_ADDRESS ON CUSTOMER.ID = CUST_ADDRESS.CUSTOMER_ID AND SHIPPER.SHIP_TO_ADDR_NO = CUST_ADDRESS.ADDR_NO
WHERE (RECEIVABLE.INVOICE_DATE >= CONVERT(DATETIME, '2004-01-01 00:00:00', 102)
AND RECEIVABLE.INVOICE_DATE <= CONVERT(DATETIME, '2004-08-01 00:00:00', 102))) a
GROUP BY COUNT_STATE
Option 2
Use GROUP BY ISNULL(CUST_ADDRESS.STATE, CUSTOMER.STATE) in the original query
-
First option:
Make 1st view and in that view calculate COUNT_STATE. Then write the 2nd view on top of the 1st view and here you can do your GROUP by clause on COUNT_STATE.
2nd Option:
Write a UDF like GetCountSTATE(CUST_ADDRESS.STATE) and do the case statement in that UDF. Then you can do the GROUP BY on GetCountSTATE(CUST_ADDRESS.STATE).
-
Perfect!
ISNULL was the option I was looking for, the same as an IIF in VB. I also did not know that you had to have the whole ISNULL phrase in the GROUP BY statement.
Thank you Jean-Pierre and all other reponders! This solution makes my project so much easier.
Kindest Regards,Sean Wyatt
seanwyatt.com -
The syntax above (ISNULL) is simplier than your initial CASE, but you could also use it in the Group by :
Select ...
GROUP BY CASE
WHEN CUST_ADDRESS.STATE IS NULL THEN CUSTOMER.STATE
ELSE CUST_ADDRESS.STATE
END
ciao...
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply