SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 sum of record values with same id_number ?
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

lukael82
Starting Member

Slovenia
5 Posts

Posted - 12/10/2013 :  12:34:33  Show Profile  Reply with Quote
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

Sweden
30242 Posts

Posted - 12/10/2013 :  16:43:34  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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
Go to Top of Page

lukael82
Starting Member

Slovenia
5 Posts

Posted - 12/10/2013 :  18:27:17  Show Profile  Reply with Quote
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

Edited by - lukael82 on 12/10/2013 18:28:09
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30242 Posts

Posted - 12/12/2013 :  13:39:57  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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
Go to Top of Page

lukael82
Starting Member

Slovenia
5 Posts

Posted - 12/12/2013 :  15:30:28  Show Profile  Reply with Quote
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

Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000