Distinct SQL Query 1

[lyric0n]

Hello,

I have data in the following format and I need a distinct count of users. I have a way to come up with the grouping, but making it distinct is causing me issues.

ID | Device | Method | Username
1 | Alpha | X | smith
2 | Beta | X | smith
3 | Beta | Y | smith
4 | Alpha | Z | john

Results:

Method MethodCount
X 1
Y 1
Z 1

Thanks for any help,
Chris

 

[gmmastros]

Try this...

[tt][blue]
Select Method, Count(UserName)
From (
Select Distinct Method, UserName
From Table
) As A
Group By A.Method
[/blue][/tt]

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[AlexCuse]

You want to join to a subquery. I think this will do the trick:

select a.Method, count(b.UserName) as MethodCount
from myTable a
inner join
(
select distinct method, username
from myTable
) b
on a.Method = b.Method
group by a.Method

Hiope it helps,

Alex


Ignorance of certain subjects is a great part of wisdom

 

[lyric0n]

Thanks guys! This helped a lot.

 

[lyric0n]

Would anyone have an idea how to add a percentage column to this?

Ideally it would return:

Method MethodPercent MethodCount
X .33 1
Y .33 1
Z .33 1

Then also, is there a way to group by methods that I chose in my where query, and not necesarily in the result?

So maybe...

Method MethodPercent MethodCount
W 0 0
X .33 1
Y .33 1
Z .33 1

Thanks,
Chris

 

[AlexCuse]

I think this will do it for you, might need a little formatting though:

Select a.Method, Count(a.UserName)
, (1.00 *  Count(a.UserName))/ B.TC
From   (
       Select Distinct Method, UserName
       From   myTable
       ) As A
cross join 
	(
	select count(Method) as TC from myTable
	) B
Group By A.Method, B.TC

In order to get your second request done, you will need to have a table containing all methods (because you can't show that which is not in your table)

Hope this helps,

Alex

Ignorance of certain subjects is a great part of wisdom

 

[lyric0n]

This is close, but it is dividing the count by the entire set of data, not just the unique ones. What I need is something along the lines of...


Select a.Method, Count(a.UserName)
, (1.00 * Count(a.UserName))/ B.TC
From (
Select Distinct Method, UserName
From myTable
) As A
cross join
(
select count(Method) as TC from
(
Select Distinct Method, UserName
From myTable
)
) B
Group By A.Method, B.TC

But instead this gives me an error about an invalid ')'.

 

[AlexCuse]

Try this, you needed to give an alias to your innermost subquery.

Select a.Method, Count(a.UserName)
, (1.00 *  Count(a.UserName))/ B.TC
From   (
       Select Distinct Method, UserName
       From   myTable
       ) As A
cross join 
    (
    select count(*) as TC from 
    (
       Select Distinct Method, UserName
       From   myTable
    ) [b]X[/b]
    ) B
Group By A.Method, B.TC

Hope it helps,

Alex

Ignorance of certain subjects is a great part of wisdom