Sql Group By and Sum() In One Column

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 Group By and Sum() In One Column

Author

Topic

Stark
Starting Member

7 Posts

Posted - 2010-04-20 : 13:44:45

Hi , Have a good day

I have an Sql Table Like this

MatTable

type = 1 Is IN Qty ( Buy )
type = 0 Is Out Qty ( Sell )

mat qty type
-------------------------------------------------- ---------------------- -----
mat1 10 0
mat2 2 0
mat3 10 0
mat1 5 1
mat2 5 1
mat2 7 1
mat4 4 0
mat4 4 0
mat4 8 1
mat3 6 1
mat1 10 0

How I can Get a result Like

mat Current qty
-------------------------------------------------- ----------------------
mat1 15
mat2 -10
mat3 4
mat4 0

Thank you In Advance

I know nothing , I know nothing ....

Stark
Starting Member

7 Posts

Posted - 2010-04-20 : 13:46:53

Sorry for the first Post , I forget the code marks

Hi I have an Sql Table Like this

MatTable

type = 1 Is IN Qty ( Buy )
type = 0 Is Out Qty ( Sell )


mat                                                qty                    type
-------------------------------------------------- ---------------------- -----
mat1                                               10                     0
mat2                                               2                      0
mat3                                               10                     0
mat1                                               5                      1
mat2                                               5                      1
mat2                                               7                      1
mat4                                               4                      0
mat4                                               4                      0
mat4                                               8                      1
mat3                                               6                      1
mat1                                               10                     0

How I can Get a result Like


mat                                                Current qty                    
-------------------------------------------------- ----------------------
mat1                                               15                     
mat2                                               -10                    
mat3                                               4                     
mat4                                               0

Thank you In Advance

I know nothing , I know nothing ....

pk_bohra
Master Smack Fu Yak Hacker

1182 Posts

Posted - 2010-04-20 : 14:23:18

TRY THIS:

Declare @MyTable Table
(
mat varchar(50),
Qty int,
Type Bit
)

Insert into @MyTable
Select 'Mat1',10,0 UNION ALL
Select 'mat2',2,0 UNION ALL
Select 'mat3',10,0 UNION ALL
Select 'mat1',5,1 UNION ALL
Select 'mat2',5,1 UNION ALL
Select 'mat2',7,1 UNION ALL
Select 'Mat4',4,0 UNION ALL
Select 'Mat4',4,0 UNION ALL
Select 'Mat4',8,1 UNION ALL
Select 'Mat3',6,1 UNION ALL
Select 'Mat1',10,0

--
--SELECT mat Current qty
---------------------------------------------------- ----------------------
--mat1 15
--mat2 -10
--mat3 4
--mat4 0

SELECT MAT, SUM(CASE TYPE WHEN 0 THEN Qty ELSE -QTY END)
FROM @MyTable
GROUP BY MAT

Regards,
Bohra

I am here to learn from Masters and help new bees in learning.

Stark
Starting Member

7 Posts

Posted - 2010-04-20 : 14:31:58

Thank you , So much Mr.pk_bohra

It's work Like Charming ....

and the Query Sound very smart solution

I know nothing , I know nothing ....

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources