We need a full summary of the table. Example: the main large query (with the join and where heap) returns the table model, amount, price, type

We need a total of the fields amount and price: model, amount, price, type. And in the last line the totals for the columns amount, price

I tried this:

select * from ( Select mk.model, pr.amount, pr.price, mk.type from product pr left join maker mk on mk.model=pr.model where amount>10) tabl union select 'ИТОГ', sum(amount), sum(price), ' ' from tabl

Writes

tabl invalid

Tell me, what is the error?

  • First, to use union, both queries must return the same number of columns and compatible data types in the corresponding columns (from the wiki). Secondly, write the request itself completely, maybe a syntax error has crept into it. Well, in the third, specify the data types that return queries. - MrFylypenko
  • The query itself is correct, it works, and the number of columns in the columns being merged is correct. I understand the problem is that oracle can not calculate the amount in the table tabl? So how for him there is no such table? Types - model-text, amount - integer, price-integer, type-text - Elena Katz
  • True request or not, you can decide if to see it completely, and not where a bunch of dots. - 0xdb
  • Added a request - Elena Katz
  • So it won't work, try with tabl as (....) - 0xdb

2 answers 2

 select nvl(mk.model,'ИТОГ') model, sum(pr.amount) amount, sum(pr.price) price, mk.type from product pr left join maker mk on mk.model=pr.model where amount>10 group by rollup( (mk.model, mk.type) )

In group by it is necessary to specify such columns, which would ensure the uniqueness of all rows. Double parentheses are also necessary, without them subtotals will be summed up for each model. rollup in the final records makes the fields involved in the grouping NULL, in fact, on this basis, we distinguish the final record and replace the NULL with 'TOTAL'.

And the second option, I do not know whether it will be faster or not:

 with Q as( select mk.model, pr.amount, pr.price, mk.type from product pr left join maker mk on mk.model=pr.model where pr.amount>10 ) select * from Q union all select 'ИТОГ', sum(amount), sum(price),' ' from Q

PS I looked at the execution plans on a small test plate: the cost for the first option is noticeably lower than for the second. The first is done by 'group by for rollup' and one pass through the table. In the second - two separate passes on the table. But the cpu-cost is higher in the first case. So you can choose which of the options is faster only on real data in a specific situation.

UPD Option 3:

 select model, nvl(amount,sum(amount) over()), nvl(price, sum(price) over()), type from ( select mk.model, pr.amount, pr.price, mk.type from product pr left join maker mk on mk.model=pr.model where pr.amount>10 union all select 'ИТОГ', NULL, NULL, ' ' from DUAL ) A

If some of the columns in the initial data can be NULL, then nvl can be replaced with these columns with decode(model,'ИТОГ',sum(price) over(),price) . In general, the cost of this option is similar to the first, but the cpu-cost is much lower and just one pass through the table.

  • Thank you very much)) With rollup at the very left) I thought it was a unit. option The most interesting is now as faster) - Elena Katz
  • The option with rollup, as you know, is preferable, but for some reason it does not work faster with it. I drove into the test tables for a million records. With union for ~ 2 sec. returned to sqldevelopr, with rollup ~ 6 sek. The plan gave preference for cost union. tkprof showed that the union eats less than the prots, but a lot of IO, and the total time for the union ~ 8 sec., for rollup ~ 12. In general, it is necessary to solve on specific data and on a specific gland. - 0xdb
  • @ 0xdb A rollup still has group by, if there are a lot of records it will have an intermediate result (count the whole sample) to write to TEMP. The union doesn’t seem to be written to temp (although it could) and the data is processed on the fly - Mike
  • Well, yes, there is no ideal option. rollup 48M in pace wrote. - 0xdb
  • @ 0xdb By the way, what union were you talking about, which is from CTE or which is the third option? And you do not forget the union all, without all it will be worse. - Mike

Here is an example of what you want to do, but in my case, select is done 2 times:

 select mk.model, pr.amount, pr.price, mk.type from product pr left join maker mk on mk.model=pr.model where pr.amount>10 union (select 'ИТОГ', sum(pr.amount), sum(pr.price), ' ' from product pr where pr.amount>10 group by 1)
  • And brackets in the second for what? group by 1? - 0xdb