Can't get this select working...

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

Can't get this select working...

Author

Topic

Auric
Yak Posting Veteran

70 Posts

Posted - 2006-01-26 : 08:49:23

Hello,

I have two tables, one is a "header" and the other is a "Footer" for a customer DB I wrote. The footer has 4 records for each header.

I need a field off of the footer to do a 'status count' and I can't get it to work

for example

select A.Status,count(*) from Header a inner join Footer b on a.CustomerID = b.CustomerID where b.SalesRep='Joe'

Basically..I am trying to get an output of

Complete 2
Cancelled 1
In Progress 3
Waiting 1

Etc.

The problem is the footer table has 4 records for each ... and even if I use a distinct clause... It multiplies the correct result by 4.

Select * from users where clue > 0

Srinika
Master Smack Fu Yak Hacker

1378 Posts

Posted - 2006-01-26 : 09:09:10

Pls put the structres of 2 tbls & some sample data + ur expected results

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2006-01-26 : 11:28:09

quote:
Originally posted by Auric
...It multiplies the correct result by 4...

Divide the count by 4:

select A.Status,count(*)/4
from Header a inner join Footer b
on a.CustomerID = b.CustomerID
where b.SalesRep='Joe'

CODO ERGO SUM

AndyB13
Aged Yak Warrior

583 Posts

Posted - 2006-01-26 : 11:30:20

You obviously didnt come thru the front door did you www.sqlteam.com

Take a look at the top article, also read the comments. This should give you what you need

Andy

Edit: Heres the link to the article
http://www.sqlteam.com/item.asp?ItemID=23531

Beauty is in the eyes of the beerholder

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-01-26 : 19:42:29

quote:
Divide the count by 4:

Getting up on the wrong side of bed ?

select a.Status,count(distinct a.CustomerID)
from  Header a inner join Footer b
on    a.CustomerID = b.CustomerID
where b.SalesRep   = 'Joe'

----------------------------------
'KH'

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources