Help, please, I can not combine the two requests into one. Each of them displays a line with the necessary data on the total. And I want a single plate of two lines. UNION does not work:

The first:

select s_n_name, count(duration) as 'Количество', SEC_TO_TIME(sum(TIME_TO_SEC(duration))) as 'Время' from table where s_n='001111';

Second:

 select s_n_name, count(duration) as 'Количество', SEC_TO_TIME(sum(TIME_TO_SEC(duration))) as 'Время' from table where s_n='002222';
  • one
    @Mike and unless, in this case it is impossible to make one request with where s_n='001111' OR s_n='002222' ? - Alexey Shimansky
  • How exactly does UNION not work? What do you mean when you say "not working"? - Alexey Shimansky
  • When you do count (duration) for example, the output of the select is already turning into one line - Pupkin_Vasily

2 answers 2

You can use the IN operator in WHERE, grouping the results by key:

 select s_n_name, count(duration) as cnt, SEC_TO_TIME(sum(TIME_TO_SEC(duration))) as `time` from table where s_n IN ('001111', '002222') group by s_n, s_n_name;

Or OR, if you need strictly 2 results:

 select s_n_name, count(duration) as cnt_dur, SEC_TO_TIME(sum(TIME_TO_SEC(duration))) as secs from table where s_n='001111' OR s_n='002222' group by s_n, s_n_name;

Grouping is needed to redefine the constant grouping of the aggregate function COUNT - without custom grouping, the result of the query will always be one line

If you want to combine the results of two abstract queries (more complex conditions) with the same signature, use union all :

 select s_n_name, count(duration) as cnt, SEC_TO_TIME(sum(TIME_TO_SEC(duration))) as secs from table where s_n='001111' UNION ALL select s_n_name, count(duration) as cnt, SEC_TO_TIME(sum(TIME_TO_SEC(duration))) as secs from table where s_n='002222';

    Grouping will save you:

     SELECT s_n_name, COUNT(*) n, SEC_TO_TIME(SUM(TIME_TO_SEC(duration))) as t FROM _table WHERE s_n IN('001111', '002222') GROUP BY s_n;

    For debugging I used the following temporary table:

     CREATE TEMPORARY TABLE _table(s_n char(6), s_n_name char(14), duration TIME) SELECT '001111' s_n, 'name is 001111' s_n_name, '00:11:11' duration UNION ALL SELECT '001111' s_n, 'name is 001111' s_n_name, '00:11:11' duration UNION ALL SELECT '001111' s_n, 'name is 001111' s_n_name, '00:11:11' duration UNION ALL SELECT '002222' s_n, 'name is 002222' s_n_name, '00:22:22' duration UNION ALL SELECT '002222' s_n, 'name is 002222' s_n_name, '00:22:22' duration UNION ALL SELECT '002222' s_n, 'name is 002222' s_n_name, '00:22:22' duration UNION ALL SELECT '002222' s_n, 'name is 002222' s_n_name, '00:22:22' duration UNION ALL SELECT '003333' s_n, 'name is 003333' s_n_name, '00:33:33' duration UNION ALL SELECT '003333' s_n, 'name is 003333' s_n_name, '00:33:33' duration;

    You can use the union (I did not understand that it does not work for you there), but this is hell!

     SELECT s_n_name, COUNT(*) n, SEC_TO_TIME(SUM(TIME_TO_SEC(duration))) as t FROM _table WHERE s_n = '001111' UNION SELECT s_n_name, COUNT(*) n, SEC_TO_TIME(SUM(TIME_TO_SEC(duration))) as t FROM _table WHERE s_n = '002222';