Use of Count and distinct - 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

SQL Server 2000 Forums

SQL Server Development (2000)

Use of Count and distinct

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2002-05-21 : 09:12:19

Scott writes "I need to get the count of the distinct intersection of two values.

We have a visit table that includes the visit date and visit person allong with other information. I need to get the unique number of visit dates/visit person per office.

I am joining this table to another view that has a one to many relationship.

Here is the SQL but I keep getting and error near:

select distinct office_name,state_name,
Total_Visits = (select count (distinct c.visitday,c.visitorname)
from fieldvisits_schedule c where c.office_id = a.office_id
group by office_id )

from fieldVisits_Schedule a, office_info_schedule b

where a.office_id = b.office_id

and a.office_id = 1057

In this case I have an office id hardcoded but it will evenutally be a passed value

the table it is joining to office_info_schedule currently has multiple rows for office 1057.

If I remove the ,c.visitorname it works fine and help would be appreciated, I would prefer not to change the database layout"

Page47
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2002-05-21 : 09:34:13

try this . . .


select
    a.office_id,
    a(?).state_name,
    count(*) as total_visits
from
    fieldvisits_schedule a
    inner join office_info_schedule b
        on a.office_id = b.office_id
where
    a.office_id = @office_id
group by
    a.office_id,
    a(?).state_name,
    a.visitday,
    a.visitorname

<O>

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources