Need group by and counting query

Question

Suppose I have a table with threadid and itemid in it. There can be multiple entries of the itemid with the same threadid value...

The query:

SELECT DISTINCT 
       THREADID, 
       ITEMID 
  FROM THREADS 
 WHERE THREADID IN (SELECT DISTINCT THREADID FROM THREADS WHERE ITEMID = 10151)

yields:

THREADID    ITEMID
----------- --------------------
149         10000
149         10076
149         10123
149         10151
149         10225
149         10227
149         10243
149         10245
149         10282
149         10310
149         10311
149         10312
149         10350
2000        10151
2000        10243

How can I wrap that in another query with group and order by query to get:

THREADID      ITEMID
------------- ----------
149         13
2000         2

Answer 1

with FirstQuery AS 
(
SELECT DISTINCT THREADID, ITEMID FROM THREADS WHERE THREADID IN (SELECT DISTINCT THREADID FROM THREADS WHERE ITEMID = 10151)
)
SELECT THREADID, COUNT(*)
FROM FirstQuery;

Answer 2

SELECT DISTINCT THREADID, COUNT(ITEMID) AS ITEMS 
FROM THREADS 
WHERE THREADID IN (SELECT DISTINCT THREADID FROM THREADS WHERE ITEMID = 1059)
GROUP BY THREADID
ORDER BY THREADID

Answer 3

You could try

SELECT  sub.THREADID,
        COUNT(1) TOTAL
FROM    (
            SELECT  DISTINCT 
                    t.THREADID, 
                    t.ITEMID 
            FROM    THREADS t INNER JOIN
                    THREADS tt  ON  t.THREADID = tt.THREADID
                                AND tt.ITEMID = 10151
        ) sub
GROUP BY sub.THREADID
ORDER BY sub.THREADID

Answer 4

SQL Server 2005+ using CTE:

---

  WITH threads_cte AS (
    SELECT DISTINCT x.threadid 
      FROM THREADS x
     WHERE x.itemid = ?)
  SELECT t.threadid,
         COUNT(DISTINCT t.itemid) 'num_itemid'
    FROM THREADS t
    JOIN threads_cte tc ON tc.threadid = t.threadid
GROUP BY t.threadid

Non CTE Equivalent:

---

  SELECT t.threadid,
         COUNT(DISTINCT t.itemid) 'num_itemid'
    FROM THREADS t
    JOIN (SELECT DISTINCT x.threadid 
            FROM THREADS x
           WHERE x.itemid = ?) y ON y.threadid = t.threadid
GROUP BY t.threadid