-1

I am googling to find proper way to make subtotals in Oracle SQL. Recording to this i make query 

select model,  sifra, velicina, sum(nvl(magacin,0)) as suma
from podmornica
where model ='30001'
group by  sifra, velicina, cube (model)
order by model, sifra, velicina

I have table podmornica with columns:model, sifra, velicina, magacin

But it doesn't work. Every second row in column model is null, and at the end not calculate sum. How to solve this? Thanks

P.S. In one MODEL we have variations of SIFRA, i wan't as result to have subtotals for each SIFRA for one model (in this case model is 30001). Like below

MODEL  SIFRA     VELICINA  SUMA

30001  3000101      0        1
30001  3000102      0        2
30001  3000103      0        5
______________________________
30001                        8
Savke
  • 131
  • 3
  • 11
  • 3
    Please update your question to include sample data in your table, along with the expected output. It's possible that you might be after ROLLUP. If I were you, I'd read [this article](https://docs.oracle.com/cd/B19306_01/server.102/b14223/aggreg.htm) about the different types of aggregation you could do. Alternatively, you could benefit from [analytic functions](https://docs.oracle.com/cd/E11882_01/server.112/e41084/functions004.htm#SQLRF06174) to do your sub-totalling. – Boneist May 15 '17 at 12:37
  • `Group by grouping sets` if you don't need the full cube. – xQbert May 15 '17 at 12:46

2 Answers2

1

This appears to be a good time to use group by grouping sets...

SELECT MODEL,  SIFRA,     VELICINA,  sum(nvl(magacin,0)) as SUMA
FROM podmornica
WHERE model ='30001'
GROUP BY GROUPING SETS ((MODEL, SIFRA, VELICINA), (Model))

Group by the model, sifra and velicina to get the detail rows. with magacin summed by those 3 fields...

Group by model so that the sum total is shown for a given model.

Alternatively if you wanted to add a column, you could show the total on every line for the model by adding a sum(magacin) over (partition by model) as sumB to the select. This approach is using an analytic/window function.

xQbert
  • 34,733
  • 2
  • 41
  • 62
  • It works. First time hear about this, but there is a lot that i need to learn. Thank's a lot :) – Savke May 15 '17 at 13:31
  • 1
    To be fair Boneist linked the article which contained this; all I did was spell it out. – xQbert May 15 '17 at 13:32
0

It might be hard to find exactly what you want, but I think you need to cube all the columns:

select model, rgrupa, sifra, velicina, sum(nvl(magacin,0)) as suma
from podmornica
where model ='30001'
group by cube(model, rgrupa, sifra, velicina)
order by model,rgrupa, sifra, velicina
Renato Afonso
  • 654
  • 6
  • 13
  • It's not working for me... It gives me strange result – Savke May 15 '17 at 12:36
  • "Strange result". Please be more detailed. Ps: if you only want to see the subtotals for each sifra for one model, why are you selecting more columns? – Renato Afonso May 15 '17 at 12:43
  • I selecting more columns because i wan't to see all of that data. One MODEL can have more than one sifra, so i wan't to break a part of each sifra for one model. Also sifra can have more velicina, that's why i select all of this. But You have a point, it can be done without rgrupa – Savke May 15 '17 at 12:57
  • @Boneist In this time i can't upload picture. How to show here table, that isn't a picture? – Savke May 15 '17 at 12:58
  • @Smederevac pictures aren't generally welcome here. We don't need to see your actual data, just some sample data that shows a range of cases of what you're after. Best way is to construct a set of union all'd queries, along the lines of: `select 1 col1, 'a' col2, 100 col3 from dual union all select 2 id, 'b' col2, 200 col3 from dual union all...`; that way we can easily run the query to get the sample data ourselves. As for generating the expected output, generate that as a text output. – Boneist May 15 '17 at 13:18
  • @Smederevac At the very least, you should provide info as per [this question](http://stackoverflow.com/questions/41868909/calculate-running-balance-in-oracle-query) – Boneist May 15 '17 at 13:22
  • 1
    @Boneist Thank's for clearing things up. I will have this on mind, when posting a question again :) – Savke May 15 '17 at 13:32
  • @Boneist Can you tell me what's wrong now, that I can edit, and not to have -2? – Savke May 15 '17 at 14:01