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

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | 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 ?
 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
30421 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
30421 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  
 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.05 seconds. Powered By: Snitz Forums 2000