SQLTeam.com | Weblogs | Forums

I want to have totals by county

In this Query, I would like to show the total of clients by state/county. Is this possible?
I guess a running total is ok in new column or if possible a break when the state/county changes to show the total of the previous count?
So output like this>
|first_name|last_name|description|description|
VIN ULTI New York Bronx 1
MEL MORRIS New York Bronx 2
BYRAM BROWN New York Bronx 3 or preferred a line total:
*** Total Clients Bronx New York = 3
SELMA MADDISON New York Queens 1
*** Total Clients New York Queens = 1

select ac.first_name, ac.last_name, st.description, co.description from address ad
inner join all_clients_view ac
on ad.people_id = ac.people_id
inner join county co
on ad.county = co.county_id
inner join state st
on co.state_id = st.state_id
where ad.is_active = 1
order by st.description, co.description

Yes, that is possible with grouping sets:

GROUP BY (Transact-SQL) - SQL Server | Microsoft Docs

1 Like