Hello Community,
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
)
GO
The sql query is as follows:
SELECT
VW_PRODUCT.PRODUCTTYPE_DESC
,CASE
WHEN VW_PRODUCT.PRODUCTTYPE_DESC IN ('VP', 'VPD', 'VPR') THEN 'VP'
WHEN VW_PRODUCT.PRODUCTTYPE_DESC IN ('LPG') THEN 'LPG'
ELSE 'MG'
END AS FUEL_TYPE
FROM CMDM.VW_PRODUCT
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.
Cheers
Please provide some sample data and what the output should look like
yosiasz
Thanks for reaching out.
Is the following enough info to work with?

Hi Yosiasz,
Please let me know if you need more data/info in order to help me?
Can someone help me out with this question? Or let me know if I need to provide more information?
have you tested this script locally?
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
)
1 Like
use sqlteam
go
CREATE TABLE VW_PRODUCT (
BK_PRODGIFT_ID NVARCHAR(1) NULL
,BK_PRODUCTTYPE_ID NVARCHAR(15) NULL
,PRODUCT_DESC NVARCHAR(150) NULL
,PRODUCTTYPE_DESC NVARCHAR(50) NULL
)
GO
insert into VW_PRODUCT(PRODUCTTYPE_DESC)
select 'KLP' PRODUCTTYPE_DESC union
select 'JIK' union
Select 'HLP'
select COUNT(FUEL_TYPE), FUEL_TYPE From (
SELECT
VW_PRODUCT.PRODUCTTYPE_DESC
,CASE
WHEN VW_PRODUCT.PRODUCTTYPE_DESC IN ('VP', 'VPD', 'VPR') THEN 'VP'
WHEN VW_PRODUCT.PRODUCTTYPE_DESC IN ('LPG') THEN 'LPG'
ELSE 'MG'
END AS FUEL_TYPE
FROM VW_PRODUCT
) a
group by FUEL_TYPE
DROP TABLE VW_PRODUCT
1 Like
Yosiasz,
That worked fine... absolutely perfect
Thanks
you should really think of createing fuelTypeGroup table and map your VW_PRODUCT to this table using a FK fuelTypeGroup Id. Because if tomorrow a new PRODUCTTYPE_DESC comes inline VPShaka, you will count it as MG.
yosiasz, now that you mention it, I'm going to have that problem regardless. Is there a way to overcome the situation of VPShaka being counted as MG?
Or make th case statement do a like
VW_PRODUCT.PRODUCTTYPE_DESC like 'VP%'
But that open you up to all kinds of issues
hi
something like this is also possible
SQL ...
SELECT Count(CASE
WHEN vw_product.producttype_desc IN ( 'VP', 'VPD', 'VPR' ) THEN
'VP'
WHEN vw_product.producttype_desc IN ( 'LPG' ) THEN 'LPG'
ELSE 'MG'
END) AS COUNT_FUEL_TYPE
FROM vw_product
1 Like
harishgg1
This looks like the ideal solution
Such as product descriptions that might have vp in it such VP Mike Pence