count(*)+group by+having

Question

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.

Could you not just wrap that query in a select count(*) from (subquery) — Andrew, Jun 06 '18 at 07:36

score 2 · Answer 1 · answered Jun 06 '18 at 07:39

2

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

answered Jun 06 '18 at 07:39

Giorgos Betsos

71,379
9
63
98

it's answer is fine , thanks but i would like in same query add additional filter like: select count(*) from ( select ut.tr_id from un_tr ut group by ut.tr_id having count(ut.ut_id)>1 ) as t – Oleg Shevtsov Jun 06 '18 at 08:02
it answers is fine, thanks but I would like the same query to add an additional filter like select count(*) from ( select ut.tr_id from un_tr ut group by ut.tr_id having count(ut.ut_id)>1 ) as t .What do you give advice? – Oleg Shevtsov Jun 06 '18 at 08:09
@OlegShevtsov I have no picture of your actual data set and I also don't know about the purpose of the filter, so it is hard for me to give any useful advice. – Giorgos Betsos Jun 06 '18 at 08:16
Maybe it gives you some picture: select count(*) from un_tr ut where date between '2018-01-01' and '2018-03-01' ( select ut.tr_id from un_tr ut group by ut.tr_id having count(ut.ut_id)>1 ) as t This query is wrong, but here is filer. – Oleg Shevtsov Jun 06 '18 at 08:22
@OlegShevtsov Try this to see if it works: `select count(*) from ( select ut.tr_id from un_tr ut where date between '2018-01-01' and '2018-03-01' group by ut.tr_id having count(ut.ut_id)>1 ) as t` – Giorgos Betsos Jun 06 '18 at 08:30
Yes, it works, but i think i don't show all task, sorry. I would like get query like select count(*) as count_tr, count(CASE WHEN ut.ev_t = 'accident' THEN 1 END) AS u_t_a from (select distinct traveller_id as dtr_id from universe_traffic ut where date between '2018-06-01' and '2018-06-31' group by ut.traveller_id having count(ut.ut_id)>1) a and it don't work – Oleg Shevtsov Jun 06 '18 at 08:40
@OlegShevtsov Comments are not the right place trying to answer questions. I tried to answer to the question posted. If you have a different question then try writing a separate post where you explain your actual requirement. – Giorgos Betsos Jun 06 '18 at 08:44
Maybe you look out here and say any idea, thanks. https://stackoverflow.com/questions/50718866/2-countgroup-byhavingjoin – Oleg Shevtsov Jun 06 '18 at 11:08

score 1 · Answer 2 · answered Jun 06 '18 at 07:39

1

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 |
+----------+

answered Jun 06 '18 at 07:39

Jagrut Sharma

4,574
3
14
19

it answers is fine, thanks but I would like the same query to add an additional filter like select count(*) from ( select ut.tr_id from un_tr ut group by ut.tr_id having count(ut.ut_id)>1 ) as t .What do you give advice? – Oleg Shevtsov Jun 06 '18 at 08:08
You could add a `WHERE` clause in the inner query. For example, this query will only limit the `tr_id` to `2`, and give count of `1`. `select count(*) from ( select ut.tr_id from un_tr ut where d_c > 26.0 group by ut.tr_id having count(ut.ut_id) > 1 ) as t;` – Jagrut Sharma Jun 06 '18 at 18:14

count(*)+group by+having

2 Answers2