A good way to choose SUM a column with INNER JOIN'

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

General SQL Server Forums

New to SQL Server Programming

A good way to choose SUM a column with INNER JOIN'

Author

Topic

kfluffie
Posting Yak Master

103 Posts

Posted - 2009-06-01 : 15:47:23

Alright, I am bombing the forum with questions but it is okay!

This is probably an easy one but I can't find a good way (i.e. without using subqueries) to SUM the top 1 record IK.kostnad:

SELECT 
	R.Namn,
	SUM(IK.Kostnad)
FROM Recept R
INNER JOIN Ingrediens I ON I.recept_id = R.ID
INNER JOIN Råvara RV ON I.råvara_ID = RV.ID
INNER JOIN Inköp IK ON IK.råvara_ID = RV.ID
--INNER JOIN Inköp IK2 ON IK.ID = (SELECT top 1 id FROM Inköp IK WHERE råvara_id = RV.ID  ORDER BY datum desc)
WHERE R.ID = 2
GROUP BY R.Namn

IK.Inköp contains several records for the same Råvara_id but I only want the top 1 and ORDER BY 1 desc like:

SELECT top 1 Kostnad FROM Inköp
WHERE råvara_ID = XX
ORDER BY datum desc

Best Regards,
KF

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-06-01 : 15:50:10

If you are using SQL Server 2005, you can use the ROW_NUMBER() function to deal with "top 1 within group".

E 12°55'05.63"
N 56°04'39.26"

kfluffie
Posting Yak Master

103 Posts

Posted - 2009-06-01 : 15:56:46

Hi Peso!
Is ROW_NUMBER() not to advanced in this query?

For example, here is the output of this query:

SELECT 
R.Namn,
I.Antal,
I.Råvara_id
	--SUM(IK.Kostnad)
FROM Recept R
INNER JOIN Ingrediens I ON I.recept_id = R.ID
INNER JOIN Råvara RV ON I.råvara_ID = RV.ID
--INNER JOIN Inköp IK ON IK.råvara_ID = RV.ID
--INNER JOIN Inköp IK2 ON IK.ID = (SELECT top 1 id FROM Inköp IK WHERE råvara_id = RV.ID  ORDER BY datum desc)
WHERE R.ID = 2

Köttfärssås på minuten	1.00	1
Köttfärssås på minuten	400.00	29
Köttfärssås på minuten	1.00	91
Köttfärssås på minuten	1.00	103
Köttfärssås på minuten	3.00	137
Köttfärssås på minuten	1.00	139

But I also want to INNER JOIN Inköp and here is a little output from just Inköp:

SELECT * FROM Inköp
WHERE råvara_id = 1

1	2006-05-10 16:17:06.560	1.00	10.00	1	14
59	2009-01-28 12:38:59.913	25.00	10.00	1	14
62	2009-01-28 12:38:59.913	25.00	10.00	1	14

As you can see, I have several inputs for råvara_ID = 1. I would like to include inköp.kostnad but only on the last bough råvara.

Best Regards,
KF

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-06-01 : 16:14:02

[code]SELECT R.Namn,
I.Antal,
I.Råvara_id
SUM(IK.Kostnad)
FROM Recept AS R
INNER JOIN Ingrediens AS I ON I.recept_id = R.ID
INNER JOIN Råvara AS RV ON I.råvara_ID = RV.ID
INNER JOIN (
SELECT råvara_id,
kostnad,
row_number() OVER (partition by råvara_id order by datum desc) AS recid
FROM Inköp
) AS IK ON IK.råvara_ID = RV.ID
WHERE ik.recid = 1
AND r.id = 2[/code]

E 12°55'05.63"
N 56°04'39.26"

kfluffie
Posting Yak Master

103 Posts

Posted - 2009-06-01 : 16:33:22

Hi Peso,
you are sick!

As always, thank you!

The complete procedure:

ALTER PROCEDURE rakna_ut_pris (@maxpris int)
AS

SELECT	
		R.Namn,	
		SUM(IK.Kostnad)
FROM 		Recept AS R
INNER JOIN 	Ingrediens AS I ON I.recept_id = R.ID
INNER JOIN 	Råvara AS RV ON I.råvara_ID = RV.ID
INNER JOIN 	(
			SELECT	råvara_id,
				kostnad,
				row_number() OVER (partition by råvara_id order by datum desc) AS recid
			FROM	Inköp
			) AS IK ON IK.råvara_ID = RV.ID
WHERE		ik.recid = 1
			--AND r.id = 2
GROUP BY R.Namn
HAVING SUM(IK.Kostnad) <=  @maxpris
ORDER BY SUM(IK.Kostnad)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources