In Kusto (KQL), how can I restrict results to one row based on a unique pairing of two other columns?

Question

car	owner
Car	James
Bike	James
Bus	James
Bus	James
Car	Betty
Bike	Betty
Bike	Betty
Car	Mike
Car	Mike

I want the data to look like this:

Owner	Vehicles
James	3
Betty	2
Mike	1

What I need is to list out, by owner, how many total vehicles they have. Due to the way the data is stored, there are multiple entries in this table (due to updates to information on other columns not shown).

If this were SQL, I'd do something like this:

SELECT owner, SELECT count(*) from vehicles where vehicles.owner = car_records.owner) as Vehicles 
FROM car_records

I've tried a variety of combinations of let=query and joins and distincts and summarizes, but I can't figure out the right combination to get to the goal. If I could reduce the table to only one row per owner+vehicle type, then I'd be fine, but I don't know how to do that.

Answer 1

you could use the dcount() aggregation function.

for example:

datatable(car:string, owner:string)
[
    'Car', 'James', 
    'Bike', 'James', 
    'Bus', 'James', 
    'Bus', 'James', 
    'Car', 'Betty', 
    'Bike', 'Betty', 
    'Bike', 'Betty', 
    'Car', 'Mike', 
    'Car', 'Mike'
]
| summarize dcount(car) by owner

owner	dcount_car
James	3
Betty	2
Mike	1