MySQL – How to Count Rows in Table Based on Condition

MySQL

I have table, table B:

id   p.id    s.id
1      5       1
2      5       1 
3      5       2
4      5       2
5      5       3

The result looks like this:

s.id   count
1       2
2       2
3       1

I have tried:

Select count(s.id) from B group by s.id

But its not giving me the required result. I am using MySQL.

Best Answer

It's simple:

SELECT s.id
     , count(*) as COUNT
FROM B
GROUP BY s.id;

Related Solutions

Mysql – SQL Limit + count group

You can separate the problem into multiple steps.

First lets create an example table:

SQL Fiddle

MySQL 5.5.32 Schema Setup:

CREATE TABLE Visitors(
  id INT,
  -- other columns
  country CHAR(2)
);

INSERT INTO Visitors(id,country)VALUES(1,'DE');
INSERT INTO Visitors(id,country)VALUES(2,'DE');
INSERT INTO Visitors(id,country)VALUES(3,'DE');
INSERT INTO Visitors(id,country)VALUES(4,'BE');
INSERT INTO Visitors(id,country)VALUES(5,'BE');
INSERT INTO Visitors(id,country)VALUES(6,'BE');
INSERT INTO Visitors(id,country)VALUES(7,'BE');
INSERT INTO Visitors(id,country)VALUES(8,'BE');
INSERT INTO Visitors(id,country)VALUES(9,'BE');
INSERT INTO Visitors(id,country)VALUES(10,'US');
INSERT INTO Visitors(id,country)VALUES(11,'US');
INSERT INTO Visitors(id,country)VALUES(12,'US');
INSERT INTO Visitors(id,country)VALUES(13,'US');
INSERT INTO Visitors(id,country)VALUES(14,'HR');
INSERT INTO Visitors(id,country)VALUES(15,'HR');
INSERT INTO Visitors(id,country)VALUES(16,'SE');
INSERT INTO Visitors(id,country)VALUES(17,'SE');
INSERT INTO Visitors(id,country)VALUES(18,'SE');
INSERT INTO Visitors(id,country)VALUES(19,'SE');
INSERT INTO Visitors(id,country)VALUES(20,'RU');

The first step now is to count the impressions per country using the GROUP BY clause:

Query 1:

SELECT country,COUNT(1) cnt
FROM Visitors
GROUP BY country

Results:

| COUNTRY | CNT |
-----------------
|      BE |   6 |
|      DE |   3 |
|      HR |   2 |
|      RU |   1 |
|      SE |   4 |
|      US |   4 |

From there we can get the top 5 countries using LIMIT:

Query 2:

SELECT *
FROM(
  SELECT country,COUNT(1) cnt
  FROM Visitors
  GROUP BY country
)V
ORDER BY cnt DESC
LIMIT 5

Results:

| COUNTRY | CNT |
-----------------
|      BE |   6 |
|      SE |   4 |
|      US |   4 |
|      DE |   3 |
|      HR |   2 |

Now we just need to join the result back to the visitors table:

Query 3:

SELECT V.*,c.cnt 
FROM Visitors V
JOIN(
  SELECT *  
  FROM(
    SELECT country,COUNT(1) cnt
    FROM Visitors
    GROUP BY country
  )V
  ORDER BY cnt DESC
  LIMIT 5
)C
ON V.country = C.country

Results:

| ID | COUNTRY | CNT |
----------------------
|  1 |      DE |   3 |
|  2 |      DE |   3 |
|  3 |      DE |   3 |
|  4 |      BE |   6 |
|  5 |      BE |   6 |
|  6 |      BE |   6 |
|  7 |      BE |   6 |
|  8 |      BE |   6 |
|  9 |      BE |   6 |
| 10 |      US |   4 |
| 11 |      US |   4 |
| 12 |      US |   4 |
| 13 |      US |   4 |
| 14 |      HR |   2 |
| 15 |      HR |   2 |
| 16 |      SE |   4 |
| 17 |      SE |   4 |
| 18 |      SE |   4 |
| 19 |      SE |   4 |

MySQL – How to Merge 3 Tables with Conditions

As first step I merge the two product tables. I need an full outer join on product1 and product2 tables. To obtain on outer join in mysql we can union a left join and a right join. Start with a left join. In the following query IFNULL(p1.key1, p2.key1) is useful to merge two key column coming from the two product tables and IFNULL(, 'NaN') is useful to obtain 'NaN' in the output.

select IFNULL(p1.key1, p2.key1) as key1, 
       IFNULL(p1.key2, p2.key2) as key2,
       IFNULL(p1.serial, p2.serial) as serial,
       IFNULL(p1.product_data, 'NaN') as product_data1, 
       IFNULL(p2.product_data, 'NaN') as product_data2
from       product1 p1
left join  product2 p2 on p1.key1 = p2.key1 and p1.key2 = p2.key2 and p1.serial =     p2.serial;

To obtain a full outer join I need to repeat the previous query with 'right join' and union the result with the 'left join'

select IFNULL(p1.key1, p2.key1) as key1, 
       IFNULL(p1.key2, p2.key2) as key2,
       IFNULL(p1.serial, p2.serial) as serial,
       IFNULL(p1.product_data, 'NaN') as product_data1, 
       IFNULL(p2.product_data, 'NaN') as product_data2
from       product1 p1
left join  product2 p2 on p1.key1 = p2.key1 and p1.key2 = p2.key2 and p1.serial = p2.serial
union
select IFNULL(p1.key1, p2.key1) as key1, 
       IFNULL(p1.key2, p2.key2) as key2,
       IFNULL(p1.serial, p2.serial) as serial,
       IFNULL(p1.product_data, 'NaN') as product_data1, 
       IFNULL(p2.product_data, 'NaN') as product_data2
from       product1 p1
right join  product2 p2 on p1.key1 = p2.key1 and p1.key2 = p2.key2 and p1.serial = p2.serial;

For convenience, I create a view with the previous query:

create or replace view p12 as
select IFNULL(p1.key1, p2.key1) as key1, 
       IFNULL(p1.key2, p2.key2) as key2,
       IFNULL(p1.serial, p2.serial) as serial,
       IFNULL(p1.product_data, 'NaN') as product_data1, 
       IFNULL(p2.product_data, 'NaN') as product_data2
from       product1 p1
left join  product2 p2 on p1.key1 = p2.key1 and p1.key2 = p2.key2 and p1.serial = p2.serial
union
select IFNULL(p1.key1, p2.key1) as key1, 
       IFNULL(p1.key2, p2.key2) as key2,
       IFNULL(p1.serial, p2.serial) as serial,
       IFNULL(p1.product_data, 'NaN') as product_data1, 
       IFNULL(p2.product_data, 'NaN') as product_data2
from       product1 p1
right join  product2 p2 on p1.key1 = p2.key1 and p1.key2 = p2.key2 and p1.serial = p2.serial;

To check we can try with:

mysql> select * from p12 where key1 = 1 and key2 =1 ;
+------+------+--------+---------------+---------------+
| key1 | key2 | serial | product_data1 | product_data2 |
+------+------+--------+---------------+---------------+
| 1    | 1    | 0      | 15.556        | 5.556         |
| 1    | 1    | 1      | 14.996        | 4.996         |
| 1    | 1    | 2      | 12.556        | NaN           |
| 1    | 1    | 3      | 15.669        | NaN           |
+------+------+--------+---------------+---------------+

So the he final query is:

select mi.key1, mi.key2, p12.serial, mi.info1, mi.info2, mi.date, p12.product_data1, p12.product_data2 
from main_info mi 
inner join p12 on mi.key1 = p12.key1 and mi.key2 = p12.key2
order by mi.key1, mi.key2, p12.serial;


+------+------+--------+-------+-------+----------+---------------+---------------+
| key1 | key2 | serial | info1 | info2 | date     | product_data1 | product_data2 |
+------+------+--------+-------+-------+----------+---------------+---------------+
| 1    | 1    | 0      | 15    | 90    | 20120501 | 15.556        | 5.556         |
| 1    | 1    | 1      | 15    | 90    | 20120501 | 14.996        | 4.996         |
| 1    | 1    | 2      | 15    | 90    | 20120501 | 12.556        | NaN           |
| 1    | 1    | 3      | 15    | 90    | 20120501 | 15.669        | NaN           |
| 1    | 2    | 0      | 14    | 92    | 20120601 | 12.556        | 2.556         |
| 1    | 2    | 1      | 14    | 92    | 20120601 | 13.335        | 3.335         |
| 1    | 2    | 2      | 14    | 92    | 20120601 | NaN           | 2.56          |
| 1    | 2    | 3      | 14    | 92    | 20120601 | NaN           | 3.556         |
| 1    | 3    | 1      | 15    | 82    | 20120801 | 12.225        | 2.225         |
| 1    | 3    | 2      | 15    | 82    | 20120801 | 13.556        | 3.556         |
| 1    | 3    | 3      | 15    | 82    | 20120801 | 14.556        | NaN           |
| 2    | 1    | 0      | 17    | 90    | 20130302 | 12.556        | NaN           |
| 2    | 1    | 1      | 17    | 90    | 20130302 | 13.553        | NaN           |
| 2    | 1    | 2      | 17    | 90    | 20130302 | 12.335        | NaN           |
| 2    | 2    | 0      | 16    | 88    | 20130601 | NaN           | 2.556         |
| 2    | 2    | 1      | 16    | 88    | 20130601 | NaN           | 3.553         |
+------+------+--------+-------+-------+----------+---------------+---------------+

Best Answer

Related Solutions

Mysql – SQL Limit + count group

MySQL – How to Merge 3 Tables with Conditions

Related Question