Joins and count(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

General SQL Server Forums

New to SQL Server Programming

Joins and count(distinct)

Author

Topic

Movp69
Starting Member

10 Posts

Posted - 2009-03-19 : 12:14:30

hi,
i've got 700,000 distinct customer id's in the usage table. I want the output of this join to total this 700,000. With a left join i get 2.6m with this join i also get 2.6m. I get the feeling that the count(distinct) is not producing the result i need. any ideas?? Thanks. Mo

select a.Usage, c.packagedescription, count(distinct a.customerid)
from mvp1.dbo.Usage1 a
join suball_d b on a.customerid=b.customerid
join mvp1.dbo.subs_package_description c on b.packageid=c.packageid
join nameall d on b.nameid=d.nameid
group by a.usage, c.packagedescription
order by 1,2 asc

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-03-19 : 12:17:21

[code]
select a.Usage, count(distinct a.customerid)
from mvp1.dbo.Usage1 a
join suball_d b on a.customerid=b.customerid
join mvp1.dbo.subs_package_description c on b.packageid=c.packageid
join nameall d on b.nameid=d.nameid
group by a.usage
order by 1,2 asc
[/code]

Movp69
Starting Member

10 Posts

Posted - 2009-03-19 : 12:22:33

that gives me 1.7m and no package description, which i need

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-03-19 : 12:41:21

[code]select a.Usage, c.packagedescription, cnt.distcustcnt
from mvp1.dbo.Usage1 a
join suball_d b on a.customerid=b.customerid
join mvp1.dbo.subs_package_description c on b.packageid=c.packageid
join nameall d on b.nameid=d.nameid
cross join (select count(distinct customer_id) as distcustcount from mvp1.dbo.Usage1) cnt
group by a.usage, c.packagedescription
order by 1,2 asc
[/code]

Movp69
Starting Member

10 Posts

Posted - 2009-03-19 : 13:04:00

hi, that is close. it gives me the right number, but it also gives me the same number for each row. I need the total to equal that number , but each row to be a count(distinct customerid). Thanks for your help on this : P

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-03-19 : 13:06:45

what? can you ilustrate with some sample data?

Movp69
Starting Member

10 Posts

Posted - 2009-03-19 : 13:10:40

This is what it should look like.......

Usage packagedescription (No column name)
High Light use 1 disc 883
High Light use 2 disc 1117
High Light use 3 disc 1199
High Light use 4 or more disc 93
High Unlimited 1 disc 1484
High Unlimited 2 disc 4136
High Unlimited 3 disc 7064
High Unlimited 4 or more discs 362
High Unlimited unlimited 2079
Low Light use 1 disc 233608
Low Light use 2 disc 73405
Low Light use 3 disc 24120
Low Light use 4 or more disc 145

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-03-19 : 13:13:40

what does 883,1117,... designate? distinct customerid within what group?

Movp69
Starting Member

10 Posts

Posted - 2009-03-19 : 13:18:00

It designates count(distinct customerid) within the High Usage and Lightuse disc 1 group

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-03-19 : 13:45:53

sorry you dont make sense. as per your latest comment your first posted query should work. please post some sample data in following format to designate what problem you're facing if you need more help. As of now, the given info is not helping us much in understanding your problem.

http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources