WHERE spanning multiple UNION ALL statements

Question

I have a SQL select statement with multiple UNION ALL keywords connecting otherwise independent SQL statements. The query is too complex for me to do without the UNION ALL keywords.

However, I want to limit all of the subqueries to a specific date range and I don't want to write the exact same where clause at the end of each of them. Is there a way I can write one WHERE clause for all of them?

I tried the following code, but it doesn't work because the COUNT(*) phrase prevents the inputDate column from being filtered for every record. Also from a performance standpoint it is probably better to filter by the date column first if there is a way to do that.

SELECT * FROM (
    SELECT count(*), inputDate 'sel1' as name FROM tblName WHERE name IN ('n1','n2')
    UNION ALL
    SELECT count(*), inputDate, 'sel2' as name FROM tblName WHERE name IN ('n3','n4')
    UNION ALL
    SELECT count(*), inputDate, 'sel3' as name FROM tblName WHERE name IN ('n5','n6')
) names WHERE inputDate >= DATE_SUB(NOW(), INTERVAL 1 MONTH)

Refer this one http://stackoverflow.com/questions/1266666/using-union-and-count-together-in-sql-query — Ataboy Josef, Jul 28 '15 at 11:27
@AtaboyJosef That one doesn't discuss my key question here - how to use a `WHERE` clause over multiple `UNION ALL` statements. The way to solve the problem may be similar, but the questions are not. — user3413723, Jul 28 '15 at 11:43

Gordon Linoff · Accepted Answer · 2015-07-28T11:22:54.267

2

You would need to do the count(*) in the outer query:

SELECT name, count(*), inputdate
FROM (SELECT inputDate, 'sel1' as name FROM tblName WHERE name IN ('n1','n2')
      UNION ALL
      SELECT inputDate, 'sel2' as name FROM tblName WHERE name IN ('n3','n4')
      UNION ALL
      SELECT inputDate, 'sel3' as name FROM tblName WHERE name IN ('n5','n6')
     ) names
WHERE inputDate >= DATE_SUB(NOW(), INTERVAL 1 MONTH)
GROUP BY name;

EDIT:

Actually, if these are all from the same table, you don't need the union all:

select (case when name in ('n1', 'n2') then 'sel1'
             when name in ('n3', 'n4') then 'sel2'
             when name in ('n5', 'n6') then 'sel3'
        end) as grp, count(*)
from tblName
where inputDate >= DATE_SUB(NOW(), INTERVAL 1 MONTH) and
      name in ('n1', 'n2', 'n3', 'n4',' n5', 'n6')
group by grp;

edited Jul 28 '15 at 11:22

answered Jul 28 '15 at 11:20

Gordon Linoff

1,242,037
58
646
786

I need to count each of the sub-queries independently. – user3413723 Jul 28 '15 at 11:22
@user3413723 . . . That is what the `group by` does. – Gordon Linoff Jul 28 '15 at 11:23
I tried both versions. Both work, but the second version is about 20x faster! – user3413723 Jul 28 '15 at 11:34

WHERE spanning multiple UNION ALL statements

1 Answers1