Summarize data

Transact-SQL
1

Hi,

I would like to summarize the data by a maximum of 4 items in one column. Generate list numbers and Count per list
The 4 first characters of column TCOM are used to maximize the number of 4 possibles group of values.
Below I try to display what I meant to do. Hopefully somebody understands and can help.
The pipe is only a separator in below datatable.
From :
|ID| TCOM |
|1 |10-1-00-00|
|2 |10-1-00-01|
|3 |10-1-00-02|
|4 |11-0-00-00|
|5 |12-0-00-00|
|6 |12-0-12-25|
|7 |13-0-00-00|
|8 |13-1-00-00|
|9 |13-1-19-31|
|10|14-0-06-19|
|11|15-0-00-00|
|12|16-0-00-00|
|13|17-0-00-00|
|14|17-1-14-18|

To:
|List # | TCOM_Count |
| 1 | 7 |
| 2 | 5 |
| 3 | 2 |
Thanks

2

I don't understand how your results are being tallied. You said the first 4 characters of TCOM, then list something differently. Also, DDL and sample data help

declare @t table (id int, TCOM varchar(20))

insert into @t
values
(1 ,'10-1-00-00'),
(2 ,'10-1-00-01'),
(3 ,'10-1-00-02'),
(4 ,'11-0-00-00'),
(5 ,'12-0-00-00'),
(6 ,'12-0-12-25'),
(7 ,'13-0-00-00'),
(8 ,'13-1-00-00'),
(9 ,'13-1-19-31'),
(10,'14-0-06-19'),
(11,'15-0-00-00'),
(12,'16-0-00-00'),
(13,'17-0-00-00'),
(14,'17-1-14-18')

select Left(TCOM, 4), count(1)
  from @t
group by Left(TCOM, 4)
3

Hi mike01,

The end results should be like:

Declare @t2 table (List# int, TCOM_Count varchar(20))
insert into @t2
values
(1 ,'7'),
(2 ,'5'),
(3 ,'2')

Select * from @t2

Thanks!

4

same question as @mike01 asked, why should the result look like that based on the data you provided

5

HI,

I need to group by a maximum of 4 different (4 first characters) and display the count.
It represents an equipment to be shipped. Each list # should count the number of TCOM .
There can't be more than 4 different (4 first characters) types of equipment per list.

Like these represent a maximum of 4 different (4 first characters).

So the List # 1 would have a count of 7 TCOM.
'10-1-00-00'
'10-1-00-01'
'10-1-00-02'
'11-0-00-00'
'12-0-00-00'
'12-0-12-25'
'13-0-00-00'

The List # 2 would have a count of 5 TCOM
'13-1-00-00'
'13-1-19-31'
'14-0-06-19'
'15-0-00-00'
'16-0-00-00'
etc...

6

Did you try what @mike01 provided you with?

7

Yes I did but that was not exactly what I needed.
This is what the end results should look like..

Declare @t2 table (List# int, TCOM_Count varchar(20))
insert into @t2
values
(1 ,'7'),
(2 ,'5'),
(3 ,'2')

Select * from @t2

8

ok I got it now

9

This makes no sense. How are you coming up with List #1 and List #2 results? it looks you are randomly putting them together. Without any sql, can you explain how you are getting these lists? Why would 13 be split into List 1 and 2, but not 17 to list 2 and 3?

10

Hi Mike,

TCOM beginning with 10-1 is one type of an equipment, 11-0 is another one, 12-0, 13-0,13-1 etc..
Each list must be made of a maximum of 4 equipment and for each list, I need a count.

Regards!

11

try this:

Select Case when RowNum % 4 = 0 then  (RowNum / 4) - 1 else RowNum / 4 end as ListID, count(1)
 from  #t t
	join (
		select TCOM, Row_number() over (order by tcom) as RowNUm
		  from (
			select Left(TCOM, 4) as TCOM--,  Row_number() over (partition by  Left(TCOM, 4) order by tcom, id), *
			  from #t
			group by Left(TCOM, 4)
			) g) g
on  Left(t.TCOM, 4) = g.TCOM
group by  Case when RowNum % 4 = 0 then  (RowNum / 4) - 1 else RowNum / 4 end


Select Case when RowNum % 4 = 0 then  (RowNum / 4) - 1 else RowNum / 4 end as ListID,  *
 from  #t t
	join (
		select TCOM, Row_number() over (order by tcom) as RowNUm
		  from (
			select Left(TCOM, 4) as TCOM--,  Row_number() over (partition by  Left(TCOM, 4) order by tcom, id), *
			  from #t
			group by Left(TCOM, 4)
			) g
			) g
on  Left(t.TCOM, 4) = g.TCOM
1 Like
12

Hi mike01,

That would be exactly it, works great.
Thank you so much! :star_struck:

13

hi

i know mike has given you the answer !!

i was taking a look at it !!!
Here is what I have .. mine appears shorter and simpler
if i am missing something .. Please please correct me ..

; with cte as 
(
     select left(TCOM,4) as gb , count(*)  as List 	from @t   group by  left(TCOM,4)
)
select    List ,sum(list) as TCOM_Count from  cte group by List

image
image
image