Or Arbel Or Arbel - 1 month ago 7
SQL Question

Postgres - SQL query count by 2 columns

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 Source

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
;