Query help - SQL Server Forums

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

Query help

Author

Topic

gfaryd
Starting Member

27 Posts

Posted - 2011-04-07 : 05:42:31

Dear All
I have following table structure in Sql server 2000

Create table test
(
pid varchar(10),
sysid integer,
remarks varchar(10),
total integer
)

sample data

insert into test('A001',4,null,1)
insert into test('A001',4,null,1)
insert into test('A001',4,'abc',2)
insert into test('A001',5,null,1)
insert into test('B001',3,null,1)
insert into test('B001',4,null,1)
insert into test('B001',4,'abc',2)
insert into test('B001',7,'pqr',3)
insert into test('B001',7,'pqr',3)
insert into test('C001',9,null,3)
insert into test('C001',9,null,3)
insert into test('C001',5,'abc',4)
insert into test('C001',7,'pqr',3)
insert into test('C001',7,'pqr',3)
insert into test('C001',7,'xyz',3)
insert into test('C001',7,null,2)

following query give me resut

select pid, sum(count(*) from test
group by 1

A001 5
B001 10
C001 21

however i want result

A001 4
B001 7
C001 15

on following criteria

sysid and remarks are related, if sysid and remarks are same then
only one total should be added in sum otherwise both should be added

e.g in

insert into test('A001',4,null,1)
insert into test('A001',4,null,1)
insert into test('A001',4,'abc',2)

sum should be 3 not 4 as sysid and remarks are same in row 1 and 2

in

insert into test('B001',7,'pqr',3)
insert into test('B001',7,'pqr',3)

only 3 should be added in total sum as sysid and remarks are same

in

insert into test('C001',7,'pqr',3)
insert into test('C001',7,'pqr',3)
insert into test('C001',7,'xyz',3)

row 1 and 2 are same so total should be 6 not 9

any help in query

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2011-04-07 : 05:47:19

select pid, sum(total)
from (Select distinct * From test) A
group by pid

Corey

I Has Returned!!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources