Getting the write count 1

[sonya9879]

I have a table with 5 records, 3 of them from the same
user and 2 of them from other users. I am trying to get a query that shows the total number of records(5) and the number of unique users (3).
Here is the data I have:

ID USERID VALUE1 CCC COM
------------------------------------
1 11 H1 55 TES
2 12 H1 55 TES
3 11 H1 55 TES
4 11 H1 55 TES
5 15 H1 55 TES

This is the query condition:

where value1 = 'H1'
and COM = 'TES'

This is the result I need:

TES, 55, H1, 5, 3

where 5 is the total number of records that matches the condition and 3 is the total number of unique userID.

This is the query I have that gives me the total number of records but not the userid:

SELECT a.com, a.ccc, a.value1, b.totalrecords
FROM table a LEFT OUTER JOIN
(SELECT com, count(*) as totalrecords
FROM table GROUP BY com) b ON a.com = b.com
WHERE value1 = 'H1'
AND COM = 'TES'
GROUP BY a.com,a.ccc,a.value1,b.totalrecords

This query give me:

TES, 55, H1, 5

but I am still missing the total users (3). How could I get that? I know that the query i need is basically

SELECT userID, count(*)
FROM table
GROUP BY userID

and adding that I just want all the records that matches a.com but I don;t know how to get the right syntax and if I need another type of join to include this last column.
I tried several different things before posting this but i dont get the right result yet

 

[vongrunt]

What about:

SELECT COM, CCC, VALUE1, COUNT(*), COUNT(DISTINCT USERID)
FROM myTable
WHERE VALUE1='H1' AND COM='TES'
GROUP BY COM, CCC, VALUE1

?

------
"There's a man... He's bald and wears a short-sleeved shirt, and somehow he's very important to me. I think his name is Homer."
(Jack O'Neill, Stargate)

 

[sonya9879]

I look like a stupid now, why @#$#@ I started writting a complicated query using joins when I could have a simple unique query that will do exactly what I want???

Anyway I am glad that I am not alone and always there are people like you Jack that can make me realise that I am still far from beeen a SQL guru

A star for you and thanks a lot.