CROSS APPLY with Column Alias

Transact-SQL
#1

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

#2

Please provide some sample data and what the output should look like

#3

yosiasz

Thanks for reaching out.

Is the following enough info to work with?
DATA

#4

Hi Yosiasz,

Please let me know if you need more data/info in order to help me?

#5

Can someone help me out with this question? Or let me know if I need to provide more information?

#6

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
#7 
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
#8

Yosiasz,

That worked fine... absolutely perfect

Thanks

#9

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.

#10

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?

#11

read this ^^

#12

Or make th case statement do a like

VW_PRODUCT.PRODUCTTYPE_DESC like 'VP%'

But that open you up to all kinds of issues

#13

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
#14

What kind of issues?

#15

harishgg1

This looks like the ideal solution

#16

Such as product descriptions that might have vp in it such VP Mike Pence