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
 SQL Help - Combing certain data

Author  Topic 

SQLnoobhelp
Starting Member

2 Posts

Posted - 2013-03-14 : 00:00:13
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
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-03-14 : 03:15:25

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


--
Chandu
Go to Top of Page

SQLnoobhelp
Starting Member

2 Posts

Posted - 2013-03-14 : 16:24:10
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
Go to Top of Page

Kulwant
Starting Member

4 Posts

Posted - 2013-03-14 : 19:25:36
[code] 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)

[/code]
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-03-15 : 11:42:59
[code]
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
[/code]

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

Go to Top of Page
   

- Advertisement -