CODEWITHSUNDEEP
sql
haran
haran

Reputation: 35

SQL: How to join two select query

OrderDetailID   OrderID ProductID   Quantity
1                10248    11          12
2                10248    42          10
3                10248    72           5
4                10249    14           9
5                10249    51          40

I want to sum all the quantity for each OrderID and after that i need to total up all quantity. I able to create to different query as

SELECT OrderID, SUM(Quantity) over ()AS TotalItemsOrdered
FROM OrderDetails
where OrderID in ('10248','10249')
group by OrderID;

and 

SELECT SUM(Quantity) over ()AS TotalItemsOrdered
FROM OrderDetails
where OrderID in ('10248','10249');

but i want to join both query to display as same result. Please teach me how i can join both query and display at same result. thank you

Upvotes: 0

Views: 123

Answers (2)

user5683823
user5683823

Reputation:

Not sure why you would want that format... the usual way to have totals by group and then a grand total is with GROUP BY ROLLUP - which should exist in most database products.

Using Oracle syntax below:

with inputs ( OrderDetailID, OrderID, ProductID, Quantity ) as (
       select 1, 10248, 11, 12 from dual union all
       select 2, 10248, 42, 10 from dual union all
       select 3, 10248, 72,  5 from dual union all
       select 4, 10249, 14,  9 from dual union all
       select 5, 10249, 51, 40 from dual
     )
-- end of test data; SQL query begins below this line
select   case grouping_id(orderid) when 1 then 'TOTAL' 
                                   else to_char(orderid) end as orderid,
         sum(quantity) as total_quantity
from     inputs
where    orderid in (10248, 10249)  --  assuming orderid is NUMBER, not VARCHAR
group by rollup (orderid)
order by orderid
;

ORDERID  TOTAL_QUANTITY
-------  --------------
10248                27
10249                49
TOTAL                76

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269773

I think you want this:

SELECT OrderID, SUM(Quantity) as OrderQuantity,
       SUM(SUM(Quantity)) OVER () as TotalItemsOrdered
FROM OrderDetails
WHEER OrderID in ('10248','10249')
GROUP BY OrderID;

Upvotes: 2

Related Questions