how to join two tables and count occurrences some values?

Question

I am asking to for a help because I do not know SQL very well.

I need to join two tables and count occurrences some values from second table to achieve effect like statistics table which will have coluns:

Result Table:

• country varchar(20),
• site varchar(20),
• suspected_violation long,
• confirmed_violation long,
• confirmed_no_violation long,
• not_determined long,
• total long

My Result Table needs to have first two columns (contry and site) comes from first table "Violations" and next 5 columns which will contain numbers (count) of occurrences status_id in "Violations" in each of possible values of id from Status table.

Now I can upload image of table result what I want to achieve:

So, I have existing two tables: Violations and Status.

Violations:

• id long,
• country varchar(20),
• site varchar(20),
• status_id long, <-- this is the id of status in Status table.
• ... other columns not important in this case

Status:

• id long,
• status long Column "status" have values (1-4) which are mapped to string values: Suspected Violation (1), Confirmed Violation (2), Confirmed No Violation (3), Not Determined (4)

In result of my join is to have table which should contain columns:

• from Violations table: "Country" and "Site"
• from Status table: "Suspected Violation", "Confirmed Violation", "Confirmed No Violation", "Not Determined", "Total" (where this columns are counters of occurrences in Violation table).

My pseudo code and abstract try:

SELECT v.country, v.site, count(v.status_id == 1), count(v.status_id == 2), count(v.status_id == 3), count(v.status_id == 4)
FROM Violations v JOIN Status s 
ON v.status_id=s.id
GROUP BY v.country, v.site

Pleas help me to write correct sql query, because have big problem to do that.

Answer 1

You should be able to use an aggregate function with a CASE expression to get the result:

select v.country,
    v.site,
    SUM(case when s.id = 1 then 1 else 0 end) Total_SuspectedViolations,
    SUM(case when s.id = 2 then 1 else 0 end) Total_ConfirmedViolations,
    SUM(case when s.id = 3 then 1 else 0 end) Total_ConfirmedNoViolations,
    SUM(case when s.id = 4 then 1 else 0 end) Total_NotDetermined,
    COUNT(*) Total
from violations v
inner join status s
    on v.status_id = s.id
group by v.country, v.site

See SQL Fiddle with Demo

This can also be written without the JOIN:

select v.country,
    v.site,
    SUM(case when v.status_id = 1 then 1 else 0 end) Total_SuspectedViolations,
    SUM(case when v.status_id = 2 then 1 else 0 end) Total_ConfirmedViolations,
    SUM(case when v.status_id = 3 then 1 else 0 end) Total_ConfirmedNoViolations,
    SUM(case when v.status_id = 4 then 1 else 0 end) Total_NotDetermined,
    COUNT(*) Total
from violations v
group by v.country, v.site

See SQL Fiddle with Demo