Group by and count items

[y1k]

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,

[yosiasz]

Welcome

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

grafana Table query

SELECT
  $__timeEpoch(createddate),
  count(1) as value,
  assettype as metric
FROM  assets
group by assettype, createddate
ORDER BY 2 

fake data sample

drop table assets

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


;with src
as
(
select top 10000 
case 
  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,
 case 
  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, 
          NEWID(), 
          createddate 
from src where assettype is not null

[y1k]

@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.