Search code examples
sqlsql-serverjoinselecttrim

SQL count rows after trimming

I have complex database with many rows I need to count. Some of the rows has additional 9 characters in front of main item. Here is the example:

select  trim(item.t_item) as Item, count(item.t_item) as counterItem
from ttdsls400201 main 
inner join ttccom100201 client on client.t_bpid = main.t_ofbp
inner join ttdsls401201 item on item.t_orno  = main.t_orno
WHERE client.t_nama = N'LIEBHERR TRANSP. SYST. GMBH & CO KG'  and trim(item.t_item)not like N'3%' and  trim(item.t_item) not like N'6%' and t_item like N'%LTSM-11085421-008%'
group by item.t_item,  main.t_ddat , main.t_corn,  main.t_oamt, main.t_ccur
ORDER BY counterItem desc

This select gives me the following output:

enter image description here

I am removing first 9 characters (everything before LTSM) with the following:

select  RIGHT((t_item), LEN(t_item) - 9) AS MyTrimmedColumn, count(RIGHT((t_item), LEN(t_item) - 9)) as counterItem
from ttdsls400201 main 
inner join ttccom100201 client on client.t_bpid = main.t_ofbp
inner join ttdsls401201 item on item.t_orno  = main.t_orno
WHERE client.t_nama = N'LIEBHERR TRANSP. SYST. GMBH & CO KG'  and trim(item.t_item)not like N'3%' and  trim(item.t_item) not like N'6%' and t_item like N'%LTSM-11085421-008%'
group by item.t_item,  main.t_ddat , main.t_corn,  main.t_oamt, main.t_ccur
ORDER BY counterItem desc

So I get this result:

enter image description here

The question is why the last 2 rows with counterItem=1 are not counter in the top? They are the same as the top one but they are not counter as that.

Solution

  • Instead Of using Group by item.t_item you should need to use this RIGHT((t_item), LEN(t_item) - 9)

    Because if you use this Column in your group by statement item.t_item it gets separated based on the text 'SLS000932','SLS000953'

    select  RIGHT((t_item), LEN(t_item) - 9) AS MyTrimmedColumn, count(RIGHT((t_item), 
LEN(t_item) - 9)) as counterItem
from ttdsls400201 main 
inner join ttccom100201 client on client.t_bpid = main.t_ofbp
inner join ttdsls401201 item on item.t_orno  = main.t_orno
WHERE client.t_nama = N'LIEBHERR TRANSP. SYST. GMBH & CO KG'  and trim(item.t_item)not 
like N'3%' and  trim(item.t_item) not like N'6%' and t_item like N'%LTSM-11085421-008%'
group by RIGHT((t_item), LEN(t_item) - 9),  main.t_ddat , main.t_corn,  main.t_oamt, 
main.t_ccur
ORDER BY counterItem desc