Hashim77 Hashim77 - 4 months ago 8
SQL Question

How to insert counts of rows by joining 3 tables on sql?

I am trying to join 3 tables and get counts of each contact's cases. So far I have this query written, but the result shows me contacts who has cases. I'd like to have a new column where it would show me "No of cases" for each contact, so the result should be 3 rows, 3 columns (last column is counts of cases)

Table A (Contacts)

+-----------------------+----+---------------------------------+
| Name | id | Organization |
+-----------------------+----+---------------------------------+
| Heidi Wilson-Reynolds | 2 | Alabama Arts Services |
+-----------------------+----+---------------------------------+
| Dr. Andrew Zope | 3 | Connecticut Empowerment Academy |
+-----------------------+----+---------------------------------+
| Rolando Cooper Sr. | 8 | Dutton Advocacy Trust |
+-----------------------+----+---------------------------------+


Table B (Case Contacts)

+----+---------+------------+
| id | case_id | contact_id |
+----+---------+------------+
| 1 | 1 | 2 |
+----+---------+------------+
| 2 | 2 | 3 |
+----+---------+------------+
| 3 | 3 | 8 |
+----+---------+------------+
| 4 | 4 | 2 |
+----+---------+------------+
| 5 | 5 | 3 |
+----+---------+------------+
| 6 | 6 | 8 |
+----+---------+------------+


Table C (Cases)

+----+-----------+
| id | status_id |
+----+-----------+
| 1 | 1 |
+----+-----------+
| 2 | 1 |
+----+-----------+
| 3 | 1 |
+----+-----------+
| 4 | 1 |
+----+-----------+
| 5 | 1 |
+----+-----------+
| 6 | 1 |
+----+-----------+


Query:

SELECT
A.display_name AS 'Name',
A.organization_name AS 'Organization'
FROM
A
INNER JOIN
B ON A.id = B.contact_id
INNER JOIN
C ON B.case_id = C.id
WHERE
A.contact_type = 'Individual' and C.status_id = 1; # Case status 1 is "Ongoing"


Result:

+-----------------------+---------------------------------+
| Name | Organization |
+-----------------------+---------------------------------+
| Heidi Wilson-Reynolds | Alabama Arts Services |
+-----------------------+---------------------------------+
| Dr. Andrew Zope | Connecticut Empowerment Academy |
+-----------------------+---------------------------------+
| Rolando Cooper Sr. | Dutton Advocacy Trust |
+-----------------------+---------------------------------+
| Heidi Wilson-Reynolds | Alabama Arts Services |
+-----------------------+---------------------------------+
| Dr. Andrew Zope | Connecticut Empowerment Academy |
+-----------------------+---------------------------------+
| Rolando Cooper Sr. | Dutton Advocacy Trust |
+-----------------------+---------------------------------+


This is the result I would like it to show, I think I need to use COUNT() function:

+-----------------------+---------------------------------+-----------------+
| Name | Organization | number_of_cases |
+-----------------------+---------------------------------+-----------------+
| Heidi Wilson-Reynolds | Alabama Arts Services | 2 |
+-----------------------+---------------------------------+-----------------+
| Dr. Andrew Zope | Connecticut Empowerment Academy | 2 |
+-----------------------+---------------------------------+-----------------+
| Rolando Cooper Sr. | Dutton Advocacy Trust | 2 |
+-----------------------+---------------------------------+-----------------+

Answer

Use group by

  SELECT 
      A.display_name AS 'Name',
      A.organization_name AS 'Organization',
      count(*) AS 'number_of_cases'

  FROM
      A
          INNER JOIN
      B ON A.id = B.contact_id
          INNER JOIN
      C ON B.case_id = C.id
  WHERE
      A.contact_type = 'Individual' and C.status_id = 1; # Case status 1 is "Ongoing"
  GROUP BY  A.display_name,  A.organization_name

The count(*) count the number of row that match the same value for the grouped by column .. in your case you have three distinct value for (name, Organization)

the the group by clause return this three different rows .. ad for each of this row count the number of rows that have the same value for name and Organization.

In you sample all the three value for (name, Organization) have 2 rows each ..

see this for brief tutorial for aggregation function

http://www.w3schools.com/sql/sql_functions.asp

http://www.sql-tutorial.com/sql-aggregate-functions-sql-tutorial/