Felix Feliciant Felix Feliciant - 2 months ago 7
SQL Question

how to group by, JOIN and perform aggregation correctly with an existing table

I have the following table "t1":

+----+---------+---------+-------+---------------------+
| id | name | country | isreg | time |
+----+---------+---------+-------+---------------------+
| 1 | felix | isr | 1 | 2016-10-02 15:23:32 |
| 2 | alex | rus | 0 | 2016-10-02 15:23:32 |
| 3 | avi | isr | 1 | 2016-10-02 15:23:32 |
| 4 | dima | rus | 1 | 2016-10-02 15:23:32 |
| 5 | nadav | isr | 0 | 2016-10-02 15:23:32 |
| 6 | vitaly | rus | 0 | 2016-10-02 15:23:32 |
| 7 | miko | ita | 1 | 2016-10-02 15:23:32 |
| 8 | jiji | ita | 1 | 2016-10-02 15:23:32 |
| 9 | vieri | ita | 0 | 2016-10-02 15:23:32 |
| 10 | maldini | ita | 1 | 2016-10-02 15:23:32 |
+----+---------+---------+-------+---------------------+


and what I want to achieve is this: (based on the isreg column)

+---------+------------+----------------+
| country | registered | notregistered |
+---------+------------+----------------+
| isr | 2 | 2 |
| rus | 1 | 2 |
| ita | 3 | 1 |
+---------+------------+----------------+


but I am having trouble as to how to construct this query, I want an explanation(PLEASE) in to how this can be done and what is the logic that I should follow when performing similar operations?

EDIT: I just need to count how many registraited from each country and how many are not

Answer

You appear to just want an aggregation:

select country, sum(isreg) as registered, sum(1 - isreg) as notregistered
from t1
group by country;

I'm not sure what explanation is needed. The logic seems pretty clear, and a join is not necessary.