on 05-20-2015 11:48 AM
Hi,
I have written an SQL statement as below.
SELECT GJAHR, SUM(DMBTR) AS "101_DMBTR", SUM(0) as "102_DMBTR" FROM "SAPR3"."MSEG"
where BWART = '101' and werks = '1102'
group by GJAHR
union all
SELECT GJAHR, SUM(0) AS "101_DMBTR", SUM(DMBTR) as "102_DMBTR" FROM "SAPR3"."MSEG"
where BWART = '102' and werks = '1102'
group by GJAHR
order by GJAHR desc;
and the out put i'm getting is
Here i want to club the values into a single row by year but the output i'm getting is into 2 rows.
can anyone suggest me how to solve this issue.
Regards,
Ramana.
Hello Ramana,
You could either put your query as the inner query:
SELECT GJAHR, SUM(101_DMBTR) as "101_DMBTR", SUM(102_DMBTR) AS "102_DMBTR"
FROM (<your query goes here>)
GROUP BY GJAHR
Or reformulate your query with a case:
SELECT GJAHR,
SUM(CASE WHEN BWART = '101' THEN DMBTR ELSE 0 END) AS "101_DMBTR",
SUM(CASE WHEN BWART = '102' THEN DMBTR ELSE 0 END) AS "102_DMBTR"
FROM "SAPR3"."MSEG"
WHERE WERKS = '1102'
GROUP BY GJAHR
order by GJAHR desc;
Best regards
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Hi Florian,
Thanks for your reply.
I mean to say, it's not even local type.
It's a return type of select query which don;t any declarations either locally or globally.
The same query worked when i tried with global table type.
Thanks once again.
Regards,
Ramana.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Hi Ramana,
i think your understanding of UNION ALL is wrong (which is also part of your other questions ).
UNION ALL combines the data sets which are returned by your selects. It does not make a grouping over the sets. In your selects the group clause is only applied to the single selects.
As already explaind by and me in your other question, you just have to surround your statement. For your above case:
SELECT GJAHR, SUM("101_DMBTR"), SUM("102_DMBTR") FROM
(
SELECT GJAHR, SUM(DMBTR) AS "101_DMBTR", SUM(0) as "102_DMBTR" FROM "SAPR3"."MSEG"
where BWART = '101' and werks = '1102'
union all
SELECT GJAHR, SUM(0) AS "101_DMBTR", SUM(DMBTR) as "102_DMBTR" FROM "SAPR3"."MSEG"
where BWART = '102' and werks = '1102'
)
GROUP BY GJAHR;
Best Regards,
Florian
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
87 | |
10 | |
10 | |
9 | |
7 | |
7 | |
6 | |
5 | |
4 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.