quote:

---

Originally posted by leosuth

Hi first time poster here

I have a table Table (say) with a field Value that has a number of rows such that field Value has a variety of values (lets say they are integers).

Could someone give me the SQL query to return the number of different values in Value, and also the number of occurences in Value of those different values

I tried:

select count(*) from Table group by Value

and this effectively gave the second bit of info I asked for - I really want if possible a single query to cover both and not sure how to address this.

Much appreciated

---

declare @temp table
(ID int IDENTITY(1,1),
CatId int
)

Insert into @temp (CatID) values (1)
Insert into @temp (CatID) values (3)
Insert into @temp (CatID) values (2)
Insert into @temp (CatID) values (4)
Insert into @temp (CatID) values (2)
Insert into @temp (CatID) values (4)
Insert into @temp (CatID) values (2)
Insert into @temp (CatID) values (1)
Insert into @temp (CatID) values (3)

select * from @temp

SELECT t1.CatId,t1.colcount,t2.distcount FROM
(
SELECT CatId,COUNT(*) as 'colcount'
FROM @temp
GROUP BY CatId)t1
CROSS JOIN (SELECT COUNT(DISTINCT CatId) AS 'DistCount'
            FROM @temp)t2

output
------------------------------------
--Inserted data
ID          CatId
----------- -----------
1           1
2           3
3           2
4           4
5           2
6           4
7           2
8           1
9           3


--result
CatId       colcount    distcount
----------- ----------- -----------
1           2           4
2           3           4
3           2           4
4           2           4