I received some help with previous question where I asked how to execute a calculation on a Column Alias. However, I couldn't make the example work for me. Therefore, can someone take a look at the following SQL script and let me know how to apply the count aggregate on the column alias called FUEL_TYPE.
The table is as follows:
CREATE TABLE CMDM.VW_PRODUCT (
,BK_PRODGIFT_ID NVARCHAR(1) NULL
,BK_PRODUCTTYPE_ID NVARCHAR(15) NULL
,PRODUCT_DESC NVARCHAR(150) NULL
,PRODUCTTYPE_DESC NVARCHAR(50) NULL
The sql query is as follows:
WHEN VW_PRODUCT.PRODUCTTYPE_DESC IN ('VP', 'VPD', 'VPR') THEN 'VP'
WHEN VW_PRODUCT.PRODUCTTYPE_DESC IN ('LPG') THEN 'LPG'
END AS FUEL_TYPE
I would like to execute the count aggregate on the column alias FUEL_TYPE to write something like:
,COUNT(FUEL_TYPE) AS TotalFuelType
Obviously, the count aggregate above won't work and will provide an error message that it can't do a count on column FUEL_TYPE because its not recognised as a column.
Any help will be greatly appreciated.