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
 General SQL Server Forums
 New to SQL Server Programming
 SQL Help - Combing certain data
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

SQLnoobhelp
Starting Member

New Zealand
2 Posts

Posted - 03/14/2013 :  00:00:13  Show Profile  Reply with Quote
Hi,

I have a set of data that looks like this:

A S 15
B T 20
C S 33
A S 28

I need a set of code when the first column has 'A' and the second column has 'S', then they will sum the numbers in the third column. However, it still need to show B and C rows.

E.g. the result I'd like to see is:

A S 43
B T 20
C S 33

Is there anyway I can do that?

Much appreciated!
SQLNoobHelp

bandi
Flowing Fount of Yak Knowledge

India
2168 Posts

Posted - 03/14/2013 :  03:15:25  Show Profile  Reply with Quote

SELECT col1, col2, SUM(col3) Sums
FROM TableName
GROUP BY col1, col2


--
Chandu
Go to Top of Page

SQLnoobhelp
Starting Member

New Zealand
2 Posts

Posted - 03/14/2013 :  16:24:10  Show Profile  Reply with Quote
Hey Bandi,

Thanks very much for replying to my query =)

However, just to make it a bit more complicated, it is only 'A' and 'S' that I would like to combine. So a data would look like:

10001 A S 15
10002 B T 20
10003 C S 33
10004 A S 28
10005 B S 35
10006 C T 30
10007 B T 33

Everything else stays the same other than combining A and S (and taking the 1st column of number to the earliest column)

So the result will look like:

10001 A S 43 (10001 A S 15 + 10004 A S 28)
10002 B T 20
10003 C S 33
10005 B S 35
10006 C T 30
10007 B T 33

Sorry for the complication and hope you can help!
Cheers,
SQLNoobHelp

Edited by - SQLnoobhelp on 03/14/2013 16:24:41
Go to Top of Page

Kulwant
Starting Member

Australia
4 Posts

Posted - 03/14/2013 :  19:25:36  Show Profile  Reply with Quote
Table name : SQLnoobhelp 
Id    Name	Category	Quantity
10001 	A 	S 		15
10002 	B 	T 		20
10003 	C 	S 		33
10004 	A 	S 		28
10005 	B 	S 		35
10006	C 	T 		30
10007 	B 	T 		33

Answer:
(SELECT Id, Name, Category, sum(Quantity) as Quantity
FROM SQLnoobhelp
WHERE Name LIKE 'A' and Category LIKE 'S'
GROUP BY Name, Category
ORDER BY Id )

UNION

(SELECT Id, Name, Category, Quantity 
FROM SQLnoobhelp
WHERE Name NOT LIKE 'A' or Category NOT LIKE 'S'
ORDER BY Id)

Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52249 Posts

Posted - 03/15/2013 :  11:42:59  Show Profile  Reply with Quote

SELECT  CASE WHEN col1='A' AND col2='S' THEN 1 ELSE Seq END AS Grp,col1,col2,sum(Quantity) AS Total
FROM (SELECT ROW_NUMBER() OVER (PARTITION BY col1,col2 ORDER BY ID) AS Seq,*
FROM Table
)t
GROUP BY CASE WHEN col1='A' AND col2='S' THEN 1 ELSE Seq END,col1,col2 


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

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.06 seconds. Powered By: Snitz Forums 2000