CODEWITHSUNDEEP
mysqlsqlgroup-bycounthaving
Oleg Shevtsov
Oleg Shevtsov

Reputation: 73

count(*)+group by+having

CREATE TABLE IF NOT EXISTS `un_tr` (
  `ut_id` int(11) NOT NULL AUTO_INCREMENT,
  `date` date DEFAULT NULL,
  `s_p_c` double NOT NULL,
  `d_c` double NOT NULL,
  `tr_id` int(11) DEFAULT NULL,
  `ev_t` varchar(50) DEFAULT NULL,

  PRIMARY KEY (`ut_id`)
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8;


INSERT INTO `un_tr` (`ut_id`, `date`, 
`s_p_c`, `d_c`, `tr_id`, `ev_type`) VALUES
(1, '2018-06-01', 20.33333, 21.33333, 1, 'accident', NULL),
(2, '2018-07-02', 21.33333, 23.33333, 1, 'accident', NULL),
(3, '2018-06-03', 21.33333, 24.33333, 1, 'accident', NULL),
(4, '2018-06-04', 25.33333, 26.33333, 1, 'travel', NULL),
(5, '2018-06-04', 21.33333, 26.33333, 2, 'travel', NULL),
(6, '2018-06-04', 21.33333, 26.33333, 2, 'accident', NULL),
(7, '2018-06-04', 21.33333, 26.33333, 2, 'travel', NULL),
(8, '2018-06-04', 21.33333, 26.33333, 3, 'travel', NULL),
(9, '2018-08-04', 19.33333, 26.33333, 4, 'travel', NULL);

and I need get just one record count(ut.tr_id)

select  count(distinct ut.tr_id) as count_tr from un_tr ut group  by ut.tr_id having count(ut.ut_id)>1

but I got the result : 

count_tr
1
1

I would like to get the result:

count_tr
2

Would you like give me advice, how do i may do it? Thanks.

Upvotes: 1

Views: 62

Answers (2)

Jagrut Sharma
Jagrut Sharma

Reputation: 4754

You could try this query:

select count(*) as count_tr from (
   select distinct tr_id as dtr_id, count(ut_id)
   from un_tr 
   group by dtr_id
   having count(ut_id) > 1
  ) a;

Result:

+----------+
| count_tr |
+----------+
|        2 |
+----------+

Upvotes: 1

Giorgos Betsos
Giorgos Betsos

Reputation: 72175

You query 

select  count(distinct ut.tr_id) as count_tr 
from un_tr ut 
group  by ut.tr_id 
having count(ut.ut_id)>1

can be simplified to

select  ut.tr_id
from un_tr ut 
group  by ut.tr_id 
having count(ut.ut_id)>1

since there is no point in doing a count(distinct ut.tr_id): the group by ut.tr_id has as a consequence that this count is always 1 per ut.tr_id.

You can now count the number of distinct tr_id occurrences just by:

select count(*)
from
(
   select  ut.tr_id
   from un_tr ut 
   group  by ut.tr_id 
   having count(ut.ut_id)>1

) as t

Upvotes: 2

Related Questions