sum of record values with same id_number ? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

sum of record values with same id_number ?

Author

Topic

lukael82
Starting Member

5 Posts

Posted - 2013-12-10 : 12:34:33

Hello !

I have these two tables

1. NAROCILO

ŠIFRA_NAROCILA |
12489................|
12491................|
12494................|
12495................|
12498................|
12500................|
12504................|

2. POSTAVKA_NAROCILA

ŠIFRA_NAROCILA | ŠTEVILO_NAROCENIH | PRODAJNA_CENA
12489................| 11..........................| 14.95
12491............... | 01..........................| 402.99
12491............... | 01..........................| 311.95
12494............... | 04..........................| 175.00
12495............... | 02..........................| 57.95
12498............... | 02..........................| 22.95
12498............... | 04..........................| 4.95
12500............... | 01..........................| 402.99
12504............... | 02..........................| 108.99

PROBLEM : I must select NAROCILO.ŠIFRA.NAROCILA, VREDNOST (which is ŠTEVILO_NAROCENIH* PRODAJNA_CENA in POSTAVKA_NAROCILA) and give result. But in POSTAVKA_NAROCILA there are 4 same values of ŠIFRA_NAROCILA (12491 and 12498) and their result of VREDNOST must be SUM-ed in final result for NAROCILO.ŠIFRA_NAROCILA. In other words, there should be 7 final results for VREDNOST and NAROCILO.ŠIFRA.NAROCILA (same as in table NAROCILO).

My code :

select šifra_narocila, (število_narocenih*prodajna_cena)as vrednost
from postavka_narocila
order by šifra_narocila DESC;

This code is wrong and It shows 9 results, because It is based only on one table. But when I performed JOIN method for both tables, I couldn't SUM that 4 same values in POSTAVKA_NAROCILO :(

Please help me !

Regards, Luka

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2013-12-10 : 16:43:34

SELECT t1.Col1, SUM(t2.Col2)
FROM dbo.Table1 AS t1
INNER JOIN dbo.Table2 ON t2 ON t2.Col1 = t1.Col1
GROUP BY t1.Col1;

Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA

lukael82
Starting Member

5 Posts

Posted - 2013-12-10 : 18:27:17

quote:
Originally posted by SwePeso

SELECT t1.Col1, SUM(t2.Col2)
FROM dbo.Table1 AS t1
INNER JOIN dbo.Table2 ON t2 ON t2.Col1 = t1.Col1
GROUP BY t1.Col1;

Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA

Thanks, It didn't work quite as you wrote, but you were very very close. Correct code is :

select NAROCILO.ŠIFRA_NAROCILA, SUM (POSTAVKA_NAROCILA.ŠTEVILO_NAROCENIH*PRODAJNA_CENA ) as vrednost
from NAROCILO, POSTAVKA_NAROCILA
where NAROCILO.šifra_narocila = POSTAVKA_NAROCILA.šifra_narocila
group by NAROCILO.šifra_narocila
order by NAROCILO.šifra_narocila desc;

or in your interpretation:

select t1.Col1, sum (t2.Col1*t2.Col2) as vrednost
from dboTable1 as t1
INNER JOIN dboTable2 as t2 on t2.Col1 = t1.Col1
group by t1.Col1;

THANKS AGAIN, YOU SAVED MY RUINED DAY

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2013-12-12 : 13:39:57

[code]SELECT a.ŠIFRA_NAROCILA,
SUM(b.ŠTEVILO_NAROCENIH * b.PRODAJNA_CENA) AS vrednost
FROM dbo.NAROCILO AS a
INNER JOIN dbo.POSTAVKA_NAROCILA AS b ON b.šifra_narocila = a.šifra_narocila
GROUP BY a.šifra_narocila
ORDER BY a.šifra_narocila DESC;[/code]

Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA

lukael82
Starting Member

5 Posts

Posted - 2013-12-12 : 15:30:28

Thanks again, guess you didn't read my post, I allready resolved problem :)

quote:

Originally posted by SwePeso

SELECT		a.ŠIFRA_NAROCILA,
		SUM(b.ŠTEVILO_NAROCENIH * b.PRODAJNA_CENA) AS vrednost 
FROM		dbo.NAROCILO AS a
INNER JOIN	dbo.POSTAVKA_NAROCILA AS b ON b.šifra_narocila = a.šifra_narocila
GROUP BY	a.šifra_narocila
ORDER BY	a.šifra_narocila DESC;

Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources