sql server - SQL count rows after trimming

Question

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:

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:

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.

question from:https://stackoverflow.com/questions/66059389/sql-count-rows-after-trimming

Answer 1

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