SQL multiple group by 1

[Willi]

how can a multiple group by query be efficiently arranged?

the query

select A1,Z,count(*) from D group by A1,Z union
select A2,Z,count(*) from D group by A2,Z union
...
select An,Z,count(*) from D group by An,Z;

at least in Oracle is not optimized. One pass over D should be sufficient to compute the counts.

 

[MalcolmW]

The only reason for the group is the count. Can you get the count from a virtual table or subselect? (Your example is obviously simplified, so i have trouble imagining what you are trying to do in this fashion) Malcolm
wynden@telus.net
November is "be kind to dogs and programmers" month. Or is that "dogs or programmers"?

 

[carp]

Try this:

SELECT A, Z, count(*)
FROM
(select A1 A,Z from D
union
select A2 A,Z from D
union
...
select An A,Z from D)
GROUP BY A,Z;