CODEWITHSUNDEEP
mysql
luqita
luqita

Reputation: 4077

Counting with conditions

I have a table with different values for different countries, for example:

id| country  |
===================
1 | Argelia  | 
2 | USA      |
1 | China    |
1 | Italy    |
1 | Italy    |
1 | USA      |
4 | USA      |
1 | Argelia  |

I am interested in only one country's count, and the total count, but I'm having trouble coming up with a single query to do it. The result of this query for id 1 would be:

id| value_in_Italy | total
==========================
1 | 2              | 6

As you can see, I obtained the value for Italy, and the total value. What kind of query would produce rows like the above for a similar table?

Upvotes: 2

Views: 104

Answers (4)

RolandoMySQLDBA
RolandoMySQLDBA

Reputation: 44343

Here is the sample data loaded

mysql> drop database if exists luqita;
Query OK, 1 row affected (0.03 sec)

mysql> create database luqita;
Query OK, 1 row affected (0.01 sec)

mysql> use luqita
Database changed
mysql> create table countrydata
    -> (
    ->     id int not null,
    ->     country varchar(32),
    ->     value int not null
    -> );
Query OK, 0 rows affected (0.10 sec)

mysql> insert into countrydata (id,country) values
    -> (1,'Argelia'  ),
    -> (2,'USA'      ),
    -> (1,'China'    ),
    -> (1,'Italy'    ),
    -> (1,'Italy'    ),
    -> (1,'USA'      ),
    -> (4,'USA'      ),
    -> (1,'Argelia'  );
Query OK, 8 rows affected, 1 warning (0.06 sec)
Records: 8  Duplicates: 0  Warnings: 1

mysql> select * from countrydata;
+----+---------+-------+
| id | country | value |
+----+---------+-------+
|  1 | Argelia |     0 |
|  2 | USA     |     0 |
|  1 | China   |     0 |
|  1 | Italy   |     0 |
|  1 | Italy   |     0 |
|  1 | USA     |     0 |
|  4 | USA     |     0 |
|  1 | Argelia |     0 |
+----+---------+-------+
8 rows in set (0.00 sec)

mysql>

Count just for Italy

select id,SUM(IF(country='Italy',1,0)) italy_count,COUNT(IF(id=1,1,0)) id_count
from countrydata WHERE id=1;

Here is the query against the sample data

mysql> select id,SUM(IF(country='Italy',1,0)) italy_count,COUNT(IF(id=1,1,0)) id_count
 ->    from countrydata WHERE id=1;
+----+-------------+----------+
| id | italy_count | id_count |
+----+-------------+----------+
|  1 |           2 |        6 |
+----+-------------+----------+
1 row in set (0.00 sec)

mysql>

Here is the all-inclusive query

select B.*,
    SUM(IF(A.country=B.country,1,0)) country_count,
    SUM(IF(B.id=A.id,1,0)) id_count
from
    countrydata A,
    (select distinct id,country from countrydata) B
group by B.id,B.country;

Here is the all-inclusive query executed

mysql> select B.*,
    ->     SUM(IF(A.country=B.country,1,0)) country_count,
    ->     SUM(IF(B.id=A.id,1,0)) id_count
    -> from
    ->     countrydata A,
    ->     (select distinct id,country from countrydata) B
    -> group by B.id,B.country;
+----+---------+---------------+----------+
| id | country | country_count | id_count |
+----+---------+---------------+----------+
|  1 | Argelia |             2 |        6 |
|  1 | China   |             1 |        6 |
|  1 | Italy   |             2 |        6 |
|  1 | USA     |             3 |        6 |
|  2 | USA     |             3 |        1 |
|  4 | USA     |             3 |        1 |
+----+---------+---------------+----------+
6 rows in set (0.00 sec)

mysql>

Upvotes: 1

PasteBT
PasteBT

Reputation: 2198

Try this:

select 1 id, sum(country = 'Italy') values_in_Italy, count(*) Total from t

Upvotes: 0

Mosty Mostacho
Mosty Mostacho

Reputation: 43434

The simplest query that works on MySQL I can think of is:

select id, sum(country = 'Italy') values_in_Italy, count(*) Total from t
where id = 1

MySQL doesn't force you to group by id as it will non-deterministically take one id, but the where clause is forcing that column to only have one id

Upvotes: 2

Teja
Teja

Reputation: 13534

SELECT A.ID,B.value_in_Italy,(SELECT COUNT(DISTINCT Country) AS total FROM YOURTABLE) AS          total
FROM YOURTABLE A,
(SELECT Country,COUNT(*) AS value_in_Italy
FROM YOURTABLE
WHERE COUNTRY='Italy'
GROUP BY Country) B
WHERE A.Country=B.Country

Upvotes: 1

Related Questions