How can I get this result? - 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 2000 Forums

Transact-SQL (2000)

How can I get this result?

Author

Topic

saglamtimur
Yak Posting Veteran

91 Posts

Posted - 2003-09-11 : 12:19:23

I have a tabe like this;

ParNo Kodu Kal Mt
1 11111 Yesil 300
1 11111 Sari 300
1 11111 Mavi 250
1 11111 Yeþil 250
1 11111 Sarý 100
1 11111 Mavi 100
2 22222 Sarý 100
2 22222 Yeþil 100
2 22222 Sarý 250
2 22222 Yeþil 250
2 22222 Mavi 100
2 22222 Mavi 250

and result must be

Kodu Yesil (Total) Sari (Total) Mavi (Total)
11111 550 400 350
22222 350 350 350

I cannot get this result. My brain stoped. Any ideas? Thanks in advance...

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-09-11 : 12:22:08

Could you provide the DDL (CREATE TABLE statement) and the DML (INSERT INTO statements) for your data so that we don't have type all of this? You will get a much faster answer if you provide everything that we need. Also, please explain in words how to get the result set.

Tara

saglamtimur
Yak Posting Veteran

91 Posts

Posted - 2003-09-11 : 13:00:58

ok here it is

CREATE TABLE [dbo].[smy] (
[part] [smallint] NULL ,
[kodu] [int] NULL ,
[kal] [char] (10) COLLATE Turkish_BIN NULL ,
[mt] [smallint] NULL
)

insert smy (part, kodu, kal, mt) values (1, 11111, 'yesil', 300)
insert smy (part, kodu, kal, mt) values (1, 11111, 'sari', 300)
insert smy (part, kodu, kal, mt) values (1, 11111, 'mavi', 250)
insert smy (part, kodu, kal, mt) values (1, 11111, 'yesil', 250)
insert smy (part, kodu, kal, mt) values (1, 11111, 'sari', 100)
insert smy (part, kodu, kal, mt) values (1, 11111, 'mavi', 100)

insert smy (part, kodu, kal, mt) values (2, 22222, 'sari', 100)
insert smy (part, kodu, kal, mt) values (2, 22222, 'yesil', 100)
insert smy (part, kodu, kal, mt) values (2, 22222, 'sari', 250)
insert smy (part, kodu, kal, mt) values (2, 22222, 'yesil', 250)
insert smy (part, kodu, kal, mt) values (2, 22222, 'mavi', 100)
insert smy (part, kodu, kal, mt) values (2, 22222, 'mavi', 250)

result must be
-Kodu | Yesil (Total)---| Sari (Total) -| Mavi (Total)
11111 | 550 ----------| 400 ---------| 350
22222 | 350 ----------| 350 ---------| 350

nr
SQLTeam MVY

12543 Posts

Posted - 2003-09-11 : 13:09:10

select
Kodu ,
saritotal = sum(case when Kal = 'Sari' then Mt else 0 end) ,
...
from tbl
group by Kodu

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

X002548
Not Just a Number

15586 Posts

Posted - 2003-09-11 : 13:10:56

Something like:


USE Northwind
GO

CREATE TABLE myTable99(ParNo int, Kodu int, Kal varchar(25), Mt Int)
GO

INSERT INTO myTable99 (ParNo, Kodu, Kal, Mt)
SELECT 1, 11111, 'Yesil', 300 UNION ALL
SELECT 1, 11111, 'Sari',  300 UNION ALL
SELECT 1, 11111, 'Mavi',  250 UNION ALL
SELECT 1, 11111, 'Yesil', 250 UNION ALL
SELECT 1, 11111, 'Sari',  100 UNION ALL
SELECT 1, 11111, 'Mavi',  100 UNION ALL
SELECT 2, 22222, 'Sari',  100 UNION ALL
SELECT 2, 22222, 'Yesil', 100 UNION ALL
SELECT 2, 22222, 'Sari',  250 UNION ALL
SELECT 2, 22222, 'Yesil', 250 UNION ALL
SELECT 2, 22222, 'Mavi',  100 UNION ALL
SELECT 2, 22222, 'Mavi',  250
GO


SELECT a.Kodu, Sum_Yesil, Sum_Sari, Sum_Mavi
  FROM    ( SELECT Kodu, SUM(Mt) AS Sum_Yesil FROM myTable99 WHERE Kal = 'Yesil'
		GROUP BY Kodu) AS a 
   	, ( SELECT Kodu, SUM(Mt) AS Sum_Sari  FROM myTable99 WHERE Kal = 'Sari'
		GROUP BY Kodu) AS b 
	, ( SELECT Kodu, SUM(Mt) AS Sum_Mavi  FROM myTable99 WHERE Kal = 'Mavi'
		GROUP BY Kodu) AS c 
 WHERE a.Kodu = b.Kodu And b.Kodu = c.Kodu
GO

DROP TABLE myTable99
GO

Perhaps?

Brett

8-)

SELECT @@POST=NewId()

That's correct! It's an AlphaNumeric!

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-09-11 : 13:15:46

[code]

SELECT kodu,
SUM(CASE WHEN kal = 'yesil' THEN mt ELSE 0 END) AS [Yesil (Total)],
SUM(CASE WHEN kal = 'sari' THEN mt ELSE 0 END) AS [Sari (Total)],
SUM(CASE WHEN kal = 'mavi' THEN mt ELSE 0 END) AS [Mavi (Total)]
FROM smy
GROUP BY part, kodu

[/code]

Tara

ann_sqlteam
Starting Member

8 Posts

Posted - 2003-09-11 : 13:16:13

SELECT Kodu,
(SELECT SUM(Mt) FROM MyTable
WHERE Kal = 'Yesil' AND MyTable.Kodu= A.Kodu) AS YesilSum
FROM MyTable A
GROUP BY Kodu

You can replicate the subquery for each value of Kodu.

ann_sqlteam
Starting Member

8 Posts

Posted - 2003-09-11 : 13:17:06

oops, I mean each value of Kal

X002548
Not Just a Number

15586 Posts

Posted - 2003-09-11 : 13:21:31

Nigel's (and Tara's) is much better and cheaper (than mine)...



SELECT Kodu
	, SUM (CASE WHEN Kal = 'Yesil' THEN Mt ELSE 0 END) AS Sum_Yesil  
	, SUM (CASE WHEN Kal = 'Sari'  THEN Mt ELSE 0 END) AS Sum_Sari  
	, SUM (CASE WHEN Kal = 'Mavi'  THEN Mt ELSE 0 END) AS Sum_Mavi
  FROM myTable99
GROUP BY Kodu 
GO

Brett

8-)

SELECT @@POST=NewId()

That's correct! It's an AlphaNumeric!

saglamtimur
Yak Posting Veteran

91 Posts

Posted - 2003-09-11 : 13:46:01

Thanks to all...

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-09-11 : 13:55:06

See how everyone rushes to help you when you make it nice and easy ?

great job, by the way, providing the DDL and sample data -- you did it perfectly. And next time, you know the trick to getting help quickly !

- Jeff

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources