Select value from multiple tables MS SQL

Question

0.00/5 (No votes)

See more:

I use this code and its ok from 1 table

with cte as (
select iznos_sa_porezom as 'Duguje', '0.00' as 'Potražuje',  
ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS RN from dbo.mp_ulazni_racun_ostalo_lista 
where redni_broj  IS NOT NULL   group by  iznos_sa_porezom
)
select  [Duguje], [Potražuje],
 (SELECT sum(Duguje-Potražuje) FROM cte t2 WHERE t2.RN <= cte.RN) AS Saldo
from cte

I need to select from multiple tables exmp 3
How to do that?

I try but dont ok

What I have tried:

with cte as (
select iznos_sa_porezom as 'Duguje', '0.00' as 'Potražuje',  
ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS RN from dbo.mp_ulazni_racun_ostalo_lista 
where redni_broj  IS NOT NULL   group by  iznos_sa_porezom
union all 
select iznos_sa_porezom as 'Duguje', '0.00' as 'Potražuje',
ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS RN  from dbo.mp_ulazni_racun_roba_lista
where redni_broj  IS NOT NULL  group by iznos_sa_porezom
)
select  [Duguje], [Potražuje],
 (SELECT sum(Duguje-Potražuje) FROM cte t2 WHERE t2.RN <= cte.RN) AS Saldo
from cte

Posted 16-Apr-20 3:32am

Goran Bibic

Updated 16-Apr-20 4:19am

Add a Solution

2 solutions

Solution 2

This would be a lot easier to work with if there was some sample data and expected results

I've never really had luck when combining the UNION ALL with a GROUP BY, and generally will break it apart into multiple CTEs.
I would start off with something like this and work with what data you have and what results you expect and the work from there.

SQL

; WITH cte1 AS (
	SELECT iznos_sa_porezom as 'Duguje'
             , '0.00' as 'Potražuje'
             ,  ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS RN
	FROM   dbo.mp_ulazni_racun_ostalo_lista 
	WHERE  redni_broj  IS NOT NULL
UNION ALL 
	SELECT iznos_sa_porezom as 'Duguje'
             , '0.00' as 'Potražuje'
             , ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS RN
	FROM   dbo.mp_ulazni_racun_roba_lista
	WHERE  redni_broj  IS NOT NULL
	
)
; WITH cte2 AS (
	SELECT   Duguje, Potražuje, RN
	FROM     cte1
	GROUP BY Duguje
)

SELECT Duguje
,      Potražuje
,      Saldo = ( SELECT Sum(Duguje-Potražuje)
                 FROM   cte2 t2
                 WHERE  t2.RN <= cte.RN)
FROM   cte2

Posted 16-Apr-20 4:20am

MadMyche

Comments

Maciej Los 16-Apr-20 11:13am

5ed!

MadMyche 16-Apr-20 11:30am

thank you

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Richard Deeming · Accepted Answer · 2020-04-16T04:08:00

It's not entirely clear what the problem is, but try:

SQL

WITH cteCombined As
(
    SELECT
        iznos_sa_porezom As [Duguje], 
        '0.00' as [Potražuje]
    FROM
        dbo.mp_ulazni_racun_ostalo_lista 
    WHERE
        redni_broj IS NOT NULL
    GROUP BY
        iznos_sa_porezom
    
    UNION ALL
    
    SELECT
        iznos_sa_porezom as [Duguje], 
        '0.00' as [Potražuje]
    FROM
        dbo.mp_ulazni_racun_roba_lista
    WHERE
        redni_broj IS NOT NULL
    GROUP BY
        iznos_sa_porezom
),
cte As
(
    SELECT
        [Duguje], 
        [Potražuje],
        ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS RN 
    FROM
        cteCombined
)
SELECT
    [Duguje], 
    [Potražuje],
    (SELECT SUM([Duguje] - [Potražuje]) FROM cte t2 WHERE t2.RN <= cte.RN) AS Saldo
FROM
    cte
;

Depending on your requirements, you might need to move the GROUP BY from cteCombined to cte.