CODEWITHSUNDEEP
mysql
Rafael Dorado
Rafael Dorado

Reputation: 1251

How to sum results of a GROUP By

I am trying to add results of a MySQL GROUP BY but I have not been able to do it, this is my code:

SELECT country,SUM(visits) AS visits,SUM(visits-1) AS repetidos, COUNT(1) AS total FROM stats GROUP BY country HAVING COUNT(1) > 1

Attached image of my result of my code and what I would like to achieve with the:

enter image description here

I will also let capture my tables:

enter image description here

Upvotes: 0

Views: 79

Answers (1)

P.Salmon
P.Salmon

Reputation: 17665

Maybe you need to add rollup

drop table if exists t;
create table t
(country varchar(2),visits int);
insert into t values
('co',12),('co',20),
('us',15),('us',1),('us',1);

SELECT country,SUM(visits) AS visits,
            SUM(visits-1) AS repetidos, 
            COUNT(1) AS total 
FROM  t 
GROUP BY country with rollup 
HAVING COUNT(1) > 1;

+---------+--------+-----------+-------+
| country | visits | repetidos | total |
+---------+--------+-----------+-------+
| co      |     32 |        30 |     2 |
| us      |     17 |        14 |     3 |
| NULL    |     49 |        44 |     5 |
+---------+--------+-----------+-------+
3 rows in set (0.00 sec)

Upvotes: 2

Related Questions