SQL qury help - SQL Server Forums

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 2005 Forums

Transact-SQL (2005)

SQL qury help

Author

Topic

saran_d28
Starting Member

36 Posts

Posted - 2009-07-02 : 01:21:10

Hi Team,

I have table like

id code amount
1 10 1000
1 20 1500
1 20 700
2 10 300
2 10 200

I need output like

id code amount
1 10 1000
1 20 2200
2 10 500
2 20 0

i need display all ids and allcocodes( if code not exists then display amount as )

I am using SQL2000.

Very urgent. Please help this

waterduck
Aged Yak Warrior

982 Posts

Posted - 2009-07-02 : 01:44:17

[code]SELECT ID, CODE, SUM(AMOUNT) AS AMOUNT
FROM TABLE
GROUP BY ID, CODE[/code]
Like tat?

AMITOFO+AMEN+YA ALLAH Hope the query works

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-07-02 : 01:48:31

Do you have another table that stores the ID and / or CODE ?

use it to LEFT JOIN to that table and then use GROUP BY similar to what waterduck has posted.

KH
[spoiler]Time is always against us[/spoiler]

melon.melon
Yak Posting Veteran

76 Posts

Posted - 2009-07-02 : 02:49:27

small help here if u have another table:


SELECT a.ID, a.CODE, SUM(a.AMOUNT) AS AMOUNT
from Table a
LEFT JOIN Table b
ON a.ID=b.ID
Group BY a.ID, a.CODE

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-07-02 : 02:55:48

I have to test my new signature...

DECLARE	@Sample TABLE
	(
		id INT,
		code INT,
		amount INT
	)

INSERT	@Sample
SELECT	1, 10, 1000 UNION ALL
SELECT	1, 20, 1500 UNION ALL 
SELECT	1, 20,  700 UNION ALL
SELECT	2, 10,  300 UNION ALL
SELECT	2, 10,  200

SELECT		i.id,
		c.code,
		SUM(COALESCE(s.amount, 0)) AS amount
FROM		(
			SELECT	DISTINCT
				id
			FROM	@Sample
		) AS i
CROSS JOIN	(
			SELECT	DISTINCT
				code
			FROM	@Sample
		) AS c
LEFT JOIN	@Sample AS s ON s.id = i.id
			AND s.code = c.code
GROUP BY	i.id,
		c.code
ORDER BY	i.id,
		c.code

Microsoft SQL Server MVP

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

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-07-02 : 03:15:56

quote:
I have to test my new signature...

Very nice signature

KH
[spoiler]Time is always against us[/spoiler]

waterduck
Aged Yak Warrior

982 Posts

Posted - 2009-07-02 : 03:28:14

quote:
Originally posted by Peso

[flower+LED+music]Microsoft SQL Server MVP[/flower+LED+music]

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

like tat? hehe
AMITOFO+AMEN+YA ALLAH Hope the query works

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-07-02 : 03:38:12

[code]
select a.id, a.code, sum(isnull(s.amount, 0))
from
(
select distinct i.id, c.code
from @Sample i
cross join @Sample c
) a
left join @Sample s on a.id = s.id and a.code = s.code
group by a.id, a.code
order by a.id, a.code
[/code]

KH
[spoiler]Time is always against us[/spoiler]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources