[xivzgrev]

Can anyone explain why the query without having needs 14 separate queries? That seemed insane to me.

It seems like the author is using one query per country. Where it seems like you’d just group by country and year, where country <> US

You would need some unions to bolt on the additional aggregations, but it’s more like 4 queues, not 14

Eg

select c.ctry_name, i.year_nbr, sum(i.item_cnt) as tot_cnt, sum(i.invoice_amt) as tot_amt from country c inner join invoice i on (i.ctry_code = c.ctry_code) where c.ctry_name <> 'USA' group by c.ctry_name, i.year_nbr

[Linosaurus]

> Can anyone explain why the query without having needs 14 separate queries? That seemed insane to me.

Yeah, four queries for the four requirements seems the most straightforward, and easier to maintain than the final version.

But still a very nice illustrating of group by cube.

[samatman]

The generous reading, which I'm inclined to, is that it's illustrating OLAP techniques which one would actually apply to a relational database of high normalization.

It's tedious to construct an example database of, say, fifth normal form, which would show the actual utility of this kind of technique. So we're left with a highly detailed query, with some redundancies which wouldn't be redundant with more tables.