Group by and count items

Im trying to group by and count items on a table (to be displayed on a pie chart). The data is gathered via an API call using infinity.

I have been able to “group by” each item, but I am not able to get a total count of each item. The group by just gives me a count of 1 for each item. I have also tried to do some other transformations, such as extract fields, but the data is not doing the count properly…

Does anyone know how to get a correct count?

I will include a screenshot of the data I’m trying to count. The result should be:
Physical Desktop count = 3
Physical Server count = 1
Virtual Server count = 1

Thank you,


Pleaae share usable sample data? Not as a screen shot but maybe csv or some other format like ddl and dml

grafana Table query

  count(1) as value,
  assettype as metric
FROM  assets
group by assettype, createddate

fake data sample

drop table assets

create table assets(assettype varchar(50), id varchar(max), 
createddate datetime)

;with src
select top 10000 
  when column_id % 2 = 0 then 'Physical Desktop' 
   when column_id % 3 = 0 then 'Physical Server' 
   when column_id % 4 = 0 then 'Virtual Server' 
   when column_id % 5 = 0 then 'Virtual Server' 
 end as assettype,
  when column_id % 2 = 0 then dateadd(dd,2,getdate() )
   when column_id % 3 = 0 then dateadd(dd,3,getdate() ) 
   when column_id % 4 = 0 then dateadd(dd,4,getdate() )
   when column_id % 5 = 0 then dateadd(dd,5,getdate() ) 
 end as createddate

 from sys.all_columns
insert into assets(assettype,id,createddate)
select assettype, 
from src where assettype is not null

@yosiasz Thank you so much for your reply and I have solved the issue with the help of your reply above.

I want to also apologize for not being able to provide any data since the data sits on an API repository that I access with authentication. Anyways, now to the solution.

Since I am using infinity as a data source to hit the API, I have selected the data I want to gather. In this case, the type (form type) of endpoints and I needed to get a total count. I tried to use transformations to get the count, but I was not able to get a correct count.

Turns out that I need to have an empty string as a second column in order to use transform to Group by, and then calculate a count. Once I added the second empty column I was able to easily group by and calculate.

1 Like