Join tables and count instances of different values

Question

user
---------------------------
| ID | Name               |
---------------------------
| 1  | Jim Rice           |
| 2  | Wade Boggs         |
| 3  | Bill Buckner       |
---------------------------

at_bats
----------------------
| ID | User |  Bases |
----------------------
| 1  | 1    | 2      |
| 2  | 2    | 1      |
| 3  | 1    | 2      |
| 4  | 3    | 0      |
| 5  | 1    | 3      |
----------------------

What I want my query to do is get the count of the different base values in a join table like:

count_of_hits
---------------------
| ID | 1B | 2B | 3B |
---------------------
| 1  | 0  | 2  | 1  |
| 2  | 1  | 0  | 0  |
| 3  | 0  | 0  | 0  |
---------------------

I had a query where I was able to get the bases individually, but not them all unless I did some complicated Joins and I'd imagine there is a better way. This was the foundational query though:

SELECT id, COUNT(ab.*)
FROM user
LEFT OUTER JOIN (SELECT * FROM at_bats WHERE at_bats.bases=2) ab ON ab.user=user.id

You want to pivot your data. There are tons of questions and answers on that here at SO. Search for "crosstab" or "pivot". — Patrick, Mar 03 '16 at 23:01
Possible duplicate of [PostgreSQL 9.3: Pivot table](http://stackoverflow.com/questions/28804776/postgresql-9-3-pivot-table) — Patrick, Mar 03 '16 at 23:25

score 1 · Answer 1 · edited May 23 '17 at 12:31

I think the following query would solve your problem. However, I am not sure if it is the best approach:

select distinct a.users, coalesce(b.B1, 0) As B1, coalesce(c.B2, 0) As B2 ,coalesce(d.B3, 0) As B3
    FROM at_bats a
    LEFT JOIN (SELECT users, count(bases) As B1 FROM at_bats WHERE bases = 1 GROUP BY users) as b ON  a.users=b.users
    LEFT JOIN (SELECT users, count(bases) As B2 FROM at_bats WHERE bases = 2 GROUP BY users) as c ON  a.users=c.users
    LEFT JOIN (SELECT users, count(bases) As B3 FROM at_bats WHERE bases = 3 GROUP BY users) as d ON  a.users=d.users
    Order by users

the coalesce() function is just to replace the nulls with zeros. I hope this query helps you :D

UPDATE 1

I found a better way to do it, look to the following:

SELECT users,
count(case bases when 1 then 1 else null end) As B1,
count(case bases when 2 then 1 else null end) As B2,
count(case bases when 3 then 1 else null end) As B3
FROM at_bats
GROUP BY users
ORDER BY users;

It it is more efficient compared to my first query. You can check the performance by using EXPLAIN ANALYSE before the query. Thanks to Guffa from this post: https://stackoverflow.com/a/1400115/4453190

score 1 · Accepted Answer · answered Mar 04 '16 at 01:58

1

PostgreSQL 9.4+ provides a much cleaner way to do this:

SELECT 
  users,
  count(*) FILTER (WHERE bases=1) As B1,
  count(*) FILTER (WHERE bases=2) As B2,
  count(*) FILTER (WHERE bases=3) As B3,
FROM at_bats
GROUP BY users
ORDER BY users;

answered Mar 04 '16 at 01:58

Robins Tharakan

2,209
19
17

Join tables and count instances of different values

2 Answers2