'Two Union from same Table-H2 Database

I am facing little problem with my query, i want to show 2 union all in single select statement, but only union all is working.

Working Query:

    select '' as Total, product, price,sellprice, 
openingstock as openingStock, openingstock*price as op_value, 
receipts as receipts, receipts*price as re_value, 
totalstock as totalstock, totalstock*price as ts_value, 
sales as sales, sales*sellprice as s_value, 
return as returns,return*sellprice as rt_value, 
closingstock as closingstock, closingstock*price as cl_value   
from purchase_table where date between '2017-04-01' and  '2017-05-30'
union all 
 select orgname , 'Total', sum(price),sum(sellprice), 
sum(openingstock) as openingStock, sum(openingstock*price) as op_value, 
sum(receipts) as receipts, sum(receipts*price) as re_value, 
sum(totalstock) as totalstock, sum(totalstock*price) as ts_value, 
sum(sales) as sales, sum(sales*sellprice) as s_value, 
sum(return) as returns,sum(return*sellprice) as rt_value, 
sum(closingstock) as closingstock, sum(closingstock*price) as cl_value   
from purchase_table  where date between '2017-04-01' and  '2017-05-30'  group by orgname order by closingstock asc

when i add another Union all its not working

select '' as Total, product, price,sellprice, 
openingstock as openingStock, openingstock*price as op_value, 
receipts as receipts, receipts*price as re_value, 
totalstock as totalstock, totalstock*price as ts_value, 
sales as sales, sales*sellprice as s_value, 
return as returns,return*sellprice as rt_value, 
closingstock as closingstock, closingstock*price as cl_value   
from purchase_table where date between '2017-04-01' and  '2017-05-30'
union all 
 select orgname , 'Total', sum(price),sum(sellprice), 
sum(openingstock) as openingStock, sum(openingstock*price) as op_value, 
sum(receipts) as receipts, sum(receipts*price) as re_value, 
sum(totalstock) as totalstock, sum(totalstock*price) as ts_value, 
sum(sales) as sales, sum(sales*sellprice) as s_value, 
sum(return) as returns,sum(return*sellprice) as rt_value, 
sum(closingstock) as closingstock, sum(closingstock*price) as cl_value   
from purchase_table  where date between '2017-04-01' and  '2017-05-30'  group by orgname order by closingstock asc 

union all
select '' , '', sum(price),sum(sellprice), 
sum(openingstock) as openingStock, sum(openingstock*price) as op_value, 
sum(receipts) as receipts, sum(receipts*price) as re_value, 
sum(totalstock) as totalstock, sum(totalstock*price) as ts_value, 
sum(sales) as sales, sum(sales*sellprice) as s_value, 
sum(return) as returns,sum(return*sellprice) as rt_value, 
sum(closingstock) as closingstock, sum(closingstock*price) as cl_value   
from purchase_table

What am doing wrong here please help

mysql h2

Solution 1:^[1]

remove order by from second and the alias from second an third select

select 
    '' as Total
    , product
    , price
    , sellprice
    , openingstock as openingStock
    , openingstock*price as op_value
    , receipts as receipts
    , receipts*price as re_value
    , totalstock as totalstock
    , totalstock*price as ts_value
    , sales as sales
    , sales*sellprice as s_value
    , return as returns
    , return*sellprice as rt_value
    , closingstock as closingstock
    , closingstock*price as cl_value   
from purchase_table 
where date between '2017-04-01' and  '2017-05-30'

union all 

 select 
    orgname 
    , 'Total'
    , sum(price)
    , sum(sellprice)
    , sum(openingstock) 
    , sum(openingstock*price) 
    , sum(receipts) 
    , sum(receipts*price) 
    , sum(totalstock) 
    , sum(totalstock*price) 
    , sum(sales)
    , sum(sales*sellprice) 
    , sum(return) 
    , sum(return*sellprice) 
    , sum(closingstock) 
    , sum(closingstock*price)    

from purchase_table  
where date between '2017-04-01' and  '2017-05-30'  
group by orgname 


union all

select 
   orgname 
   , 'Total'
   , sum(price)
   , sum(sellprice)
   , sum(openingstock) 
   , sum(openingstock*price) 
   , sum(receipts) 
   , sum(receipts*price) 
   , sum(totalstock) 
   , sum(totalstock*price) 
   , sum(sales) 
   , sum(sales*sellprice) 
   , sum(return) 
   , sum(return*sellprice) 
   , sum(closingstock) 
   , sum(closingstock*price) 
from purchase_table

and if you need the second order than use a subselect

  select 
      '' as Total
      , product
      , price
      , sellprice
      , openingstock as openingStock
      , openingstock*price as op_value
      , receipts as receipts
      , receipts*price as re_value
      , totalstock as totalstock
      , totalstock*price as ts_value
      , sales as sales
      , sales*sellprice as s_value
      , return as returns
      , return*sellprice as rt_value
      , closingstock as closingstock
      , closingstock*price as cl_value   
  from purchase_table 
  where date between '2017-04-01' and  '2017-05-30'

  union all 
   select * from 
  ( select 
      orgname 
      , 'Total'
      , sum(price)
      , sum(sellprice)
      , sum(openingstock) 
      , sum(openingstock*price) 
      , sum(receipts) 
      , sum(receipts*price) 
      , sum(totalstock) 
      , sum(totalstock*price) 
      , sum(sales)
      , sum(sales*sellprice) 
      , sum(return) 
      , sum(return*sellprice) 
      , sum(closingstock) closingstock
      , sum(closingstock*price)    

  from purchase_table  
  where date between '2017-04-01' and  '2017-05-30'  
  group by orgname  ) t order by t.closingstock asc


  union all

  select 
     orgname 
     , 'Total'
     , sum(price)
     , sum(sellprice)
     , sum(openingstock) 
     , sum(openingstock*price) 
     , sum(receipts) 
     , sum(receipts*price) 
     , sum(totalstock) 
     , sum(totalstock*price) 
     , sum(sales) 
     , sum(sales*sellprice) 
     , sum(return) 
     , sum(return*sellprice) 
     , sum(closingstock) 
     , sum(closingstock*price) 
  from purchase_table

Solution 2:^[2]

Hey scaisEdge removing order by worked for second select statement and replacing it at Last select Statement ! worked for me

select '' as Total, product, price,sellprice, 
openingstock as openingStock, openingstock*price as op_value, 
receipts as receipts, receipts*price as re_value, 
totalstock as totalstock, totalstock*price as ts_value, 
sales as sales, sales*sellprice as s_value, 
return as returns,return*sellprice as rt_value, 
closingstock as closingstock, closingstock*price as cl_value   
from purchase_table where date between '2017-04-01' and  '2017-05-30'
union all 
 select orgname , 'Total', sum(price),sum(sellprice), 
sum(openingstock) as openingStock, sum(openingstock*price) as op_value, 
sum(receipts) as receipts, sum(receipts*price) as re_value, 
sum(totalstock) as totalstock, sum(totalstock*price) as ts_value, 
sum(sales) as sales, sum(sales*sellprice) as s_value, 
sum(return) as returns,sum(return*sellprice) as rt_value, 
sum(closingstock) as closingstock, sum(closingstock*price) as cl_value   
from purchase_table  where date between '2017-04-01' and  '2017-05-30'  group by orgname 

union all
select '' , '', sum(price),sum(sellprice), 
sum(openingstock) as openingStock, sum(openingstock*price) as op_value, 
sum(receipts) as receipts, sum(receipts*price) as re_value, 
sum(totalstock) as totalstock, sum(totalstock*price) as ts_value, 
sum(sales) as sales, sum(sales*sellprice) as s_value, 
sum(return) as returns,sum(return*sellprice) as rt_value, 
sum(closingstock) as closingstock, sum(closingstock*price) as cl_value   
from purchase_table  order by closingstock asc

Solution 3:^[3]

In order to use the order by, or group by, just box this is a

select * from (...)

Here is an example I used:

SELECT * from (
  SELECT distinct(p.topiaId), COUNT(e.topiaId)
  FROM ps_common.trip e JOIN common.person p ON e.captain = p.topiaId 
  WHERE p.captain = TRUE GROUP BY p.topiaId ORDER BY p.topiaId
) UNION ALL
  SELECT 'null', count(e.topiaId)
  FROM ps_common.trip e
  WHERE e.captain IS NULL;

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution	Source
Solution 1
Solution 2	Intact Abode
Solution 3	Tony Chemit

'Two Union from same Table-H2 Database

Solution 1:[1]

Solution 2:[2]

Solution 3:[3]

Sources

Related Questions

Solution 1:^[1]

Solution 2:^[2]

Solution 3:^[3]