CODEWITHSUNDEEP
sql-server
Manoj
Manoj

Reputation: 39

How to get sql query for count by using keyword

I need a query to get total count for each unique customer id by using type names

for example,

Customer_Id     Customer_Name   Service_Type
CU00001         THANGARAJ.L     FREE SERVICE 1
CU00001         THANGARAJ.L     FREE SERVICE 2
CU00001         THANGARAJ.L     FREE SERVICE 3
CU00001         THANGARAJ.L     FREE SERVICE 4
CU00010         VARATHARAJ N    FREE SERVICE 1
CU00010         VARATHARAJ N    FREE SERVICE 2

i m using simple query like dis

select distinct(cm.CUSTOMER_ID), cm.CUST_NAME, 
(case when jm.JOBCARD_TYPE = 'FREE SERVICE 1' then 'FREE SERVICE 1'
when jm.JOBCARD_TYPE = 'FREE SERVICE 2' then 'FREE SERVICE 2'
when jm.JOBCARD_TYPE = 'FREE SERVICE 3' then 'FREE SERVICE 3'
when jm.JOBCARD_TYPE = 'FREE SERVICE 4' then 'FREE SERVICE 4'
when jm.JOBCARD_TYPE = 'PAID SERVICE 1' then 'PAID SERVICE 1'
when jm.JOBCARD_TYPE = 'PAID SERVICE 2' then 'PAID SERVICE 2'
when jm.JOBCARD_TYPE = 'PAID SERVICE 3' then 'PAID SERVICE 3'
when jm.JOBCARD_TYPE = 'PAID SERVICE 4' then 'PAID SERVICE 4'
when jm.JOBCARD_TYPE = 'PAID SERVICE 5' then 'PAID SERVICE 5'
when jm.JOBCARD_TYPE = 'PAID SERVICE 6' then 'PAID SERVICE 6' end) as SERVICE_TYPE
from [dbo].[HMSI_SATB_BOOKING] bk
left join [dbo].[HMSI_SRTB_JOBCARD_MASTER] jm
on jm.model_no = bk.MODEL_DESCR and jm.CUSTOMER_ID = bk.CUSTOMER_ID
left join [dbo].[HMSI_DMS_SERVICE_REMAINDER_SMS] sr
on sr.BOOKING_NO = bk.BOOKING_NO
left join [dbo].[HMSI_SATB_CUSTOMER_MASTER] cm
on cm.CUSTOMER_ID = bk.CUSTOMER_ID
left join [dbo].[HMSI_SATB_MODEL_MASTER] mm
on mm.MODEL_DESCR = bk.MODEL_DESCR
left join [dbo].[HMSI_DMS_SERVICE_REMAINDER] sm
on sm.MODEL_NAME = mm.MODEL_NAME where bk.INVOICE_DATE is not null and jm.JOBCARD_TYPE like '%SERVICE%'

and getting the above table

i need to get the table as

Customer_Id     Customer_Name       Count   Service_Type
CU00001         THANGARAJ.L         4       FREE SERVICE 1
CU00001         THANGARAJ.L         4       FREE SERVICE 2
CU00001         THANGARAJ.L         4       FREE SERVICE 3
CU00001         THANGARAJ.L         4       FREE SERVICE 4
CU00010         VARATHARAJ N        2       FREE SERVICE 1
CU00010         VARATHARAJ N        2       FREE SERVICE 2
CU01964         LAKSHMANAPANDI K    3       FREE SERVICE 1
CU01964         LAKSHMANAPANDI K    3       FREE SERVICE 3
CU01964         LAKSHMANAPANDI K    3       FREE SERVICE 4

can any one help me to solve dis issue plz.

Upvotes: 0

Views: 56

Answers (1)

t-clausen.dk
t-clausen.dk

Reputation: 44336

Try this:

;with x as
(
select distinct(cm.CUSTOMER_ID), cm.CUST_NAME, 
(case when jm.JOBCARD_TYPE like 'FREE SERVICE [1-4]' 
        OR jm.JOBCARD_TYPE like 'PAID SERVICE [1-6]' 
 then jm.JOBCARD_TYPE END) as SERVICE_TYPE
from [dbo].[HMSI_SATB_BOOKING] bk
left join [dbo].[HMSI_SRTB_JOBCARD_MASTER] jm
on jm.model_no = bk.MODEL_DESCR and jm.CUSTOMER_ID = bk.CUSTOMER_ID
left join [dbo].[HMSI_DMS_SERVICE_REMAINDER_SMS] sr
on sr.BOOKING_NO = bk.BOOKING_NO
left join [dbo].[HMSI_SATB_CUSTOMER_MASTER] cm
on cm.CUSTOMER_ID = bk.CUSTOMER_ID
left join [dbo].[HMSI_SATB_MODEL_MASTER] mm
on mm.MODEL_DESCR = bk.MODEL_DESCR
left join [dbo].[HMSI_DMS_SERVICE_REMAINDER] sm
on sm.MODEL_NAME = mm.MODEL_NAME where bk.INVOICE_DATE is not null and jm.JOBCARD_TYPE like '%SERVICE%'
)
SELECT Customer_Id,Customer_Name, 
count(*) over (partition by customer_id) as [Count],Service_Type
from x

Upvotes: 1

Related Questions