Inconsistant results with "Rollup" & "count distinct" in V2R6?
I've run into a problem with a report, where I'd like to use grouping sets and rollups to summarize a few hundred million transactions. Here's a test case showing my problem, but with only fifteen rows.
It starts with a two tables, one showing sales, another showing "bonus programs" that some customers subscribe to:
Let’s say we want to see some totals and counts for sales by bonus program, plus all sales that are to customers with no bonus programs:
select b.bonus_type, sum(s.price) tot, count(distinct s.cust_id) num_custs, count(distinct s.txn_date) num_days from Sales s left join Bonus_program b on s.cust_id = b.cust_id group by b.bonus_type order by b.bonus_type;
That’s perfect. Now, let’s say I want to also get a total, and do it with a rollup:
select b.bonus_type btype, grouping(b.bonus_type) grping, sum(s.price) tot, count(distinct s.cust_id) num_custs, count(distinct s.txn_date) num_days from Sales s left join Bonus_program b on s.cust_id = b.cust_id group by rollup(b.bonus_type) order by b.bonus_type;
Why are the rows with a null bonus_type doubled, with different customer counts, but other columns identical?
I've tried wrapping it up in a subselect, using "grouping sets" or "cube", and putting bonus_type inside "zeroifnull", and gotten the same results or worse. We're using "Teradata Database Release is V2R.06.01.01.97" . Edit--> Same results with V12.0 Demo, running on my laptop. If this is a bug, then it's still in later TD releases.
If you want to try it in your own db, here's the sql to create the sample tables: