CODEWITHSUNDEEP
c#sql-server-2005
Sheeba
Sheeba

Reputation: 35

sum query with distinct columns

this is my table batchmaster with columns like batchname,subbatchname,records

BatchName    SubBatch   records     
12032014     raw1_0     2  
12032014     raw1_0     2  
12032014     raw1_1     2  
12032014     raw1_1     2     
12302014     raw7_0    150  
12302014     raw7_0    150

I want to get output like with distinct batchname,subbatch and sum of records for distinct subbatch.

BatchName    SubBatch   records  
12032014     raw1_0     4  
12032014     raw1_1     4 
12302014     raw7_0   300

Upvotes: 1

Views: 51

Answers (4)

user4391070
user4391070

Reputation: 

 SELECT BatchName,SubBatch,SUM(records) AS Records
    FROM urTableName
  GROUP BY BatchName, SubBatch

Upvotes: 1

StackUser
StackUser

Reputation: 5398

I agree with Conrad Lotz query.

Try this

DECLARE @batch_master TABLE
  (
     BatchName VARCHAR(50),
     SubBatch  VARCHAR(50),
     records   INT
  )

INSERT INTO @batch_master
VALUES      ('12032014',
             'raw1_0',
             '2'),
            ('12032014',
             'raw1_0',
             '2'),
            ('12032014',
             'raw1_1',
             '2'),
            ('12032014',
             'raw1_1',
             '2'),
            ('12302014',
             'raw7_0',
             '150'),
            ('12302014',
             'raw7_0',
             '150')

SELECT *
FROM   @batch_master

SELECT batchname,
       subbatch,
       sum(records)
FROM   @batch_master
GROUP  BY batchname,
          subbatch

Upvotes: 1

varsha
varsha

Reputation: 1620

this will work for you

SELECT BatchName,SubBatch,SUM(records) AS SumRecords
FROM batchmaster
GROUP BY BatchName, SubBatch

Upvotes: 1

Conrad Lotz
Conrad Lotz

Reputation: 8818

Try the following:

SELECT BatchName,SubBatch,SUM(records) AS Records
FROM Table
GROUP BY BatchName, SubBatch

Upvotes: 0

Related Questions