Postgres - SQL query count by 2 columns

Question

I have a table in Postgres:

zone_name | trade_name   | client_name
G - WLA   | Garage Doors | King Garage Doors
J - SOC   | Attic        | Attic Jimmy
E - SGV2  | Attic        | Attic Jimmy
J - SOC   | Closets      | Brad Factory
E - SGV2  | Closets      | Brad Factory
AE - SFE  | Paint        | Chris Painting
E - SGV2  | Kitchen      | Joe Remodeling

I trying to create a table that shows how many clients (client_name) are in the same trade_name within the same zone_name.

I've been trying to use GROUP BY but couldn't figure it out.

Any ideas?

Answer 1

You can use GROUP BY on two columns. In the following query, I use group by 1, 2 -- this is a convenient way to group by on the first two columns from SELECT clause.

Also, I put two different count() to the query – probably, you will find that in your case it's more semantically correct to use count(distinct ..).

select 
  zone_name,
  trade_name,
  count(client_name) as count_clients,
  count(distinct client_name) as count_distinct_clients
from table
group by 1, 2
order by 1, 2
;

BTW, count(client_name) will not count rows, where client_name is NULL.

You can probably also find useful a new (9.5+) fancy feature, GROUPING SETS (see https://www.postgresql.org/docs/current/static/queries-table-expressions.html), which will give you counts not only for groups of (zone_name, trade_name) pairs, but for also for "single column" groups for zone_name and trade_name, in a single query (here I also use numerical order aliasing):

select 
  zone_name,
  trade_name,
  count(client_name) as count_clients,
  count(distinct client_name) as count_distinct_clients
from table
group by grouping sets ((1, 2), 1, 2)
order by 1, 2
;