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.
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 15B T 20C S 33A S 28I 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 43B T 20C S 33Is 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) SumsFROM TableNameGROUP BY col1, col2--Chandu |
|
|
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 1510002 B T 2010003 C S 3310004 A S 2810005 B S 3510006 C T 3010007 B T 33Everything 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 2010003 C S 3310005 B S 3510006 C T 3010007 B T 33Sorry for the complication and hope you can help!Cheers,SQLNoobHelp |
|
|
Kulwant
Starting Member
4 Posts |
Posted - 2013-03-14 : 19:25:36
|
[code] Table name : SQLnoobhelp Id Name Category Quantity10001 A S 1510002 B T 2010003 C S 3310004 A S 2810005 B S 3510006 C T 3010007 B T 33Answer:(SELECT Id, Name, Category, sum(Quantity) as QuantityFROM SQLnoobhelpWHERE Name LIKE 'A' and Category LIKE 'S'GROUP BY Name, CategoryORDER BY Id )UNION(SELECT Id, Name, Category, Quantity FROM SQLnoobhelpWHERE Name NOT LIKE 'A' or Category NOT LIKE 'S'ORDER BY Id)[/code] |
|
|
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 TotalFROM (SELECT ROW_NUMBER() OVER (PARTITION BY col1,col2 ORDER BY ID) AS Seq,*FROM Table)tGROUP BY CASE WHEN col1='A' AND col2='S' THEN 1 ELSE Seq END,col1,col2 [/code]------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
|
|
|
|