I have this code that sums up the qty for a certain item (itemid
) and by its product date code (proddte
).
select sum(qty), itemid, proddte
from testtable where ....
group by itemid, proddte
What I want to do is to get the total of all qty
regardless of itemid/proddte
. I have tried:
select sum(qty), itemid, proddte, sum(qty) over() as grandtotal
from testtable
where ....
group by itemid, proddte
But it says I should also have qty
in the group by
clause. If I did that, the result will not be equal to my expected result.
It does not absolutely need to be represented as a separate column, with the same value in every row. Any representation is accepted as long as I can display the overall total.