simple SUM in T-sql

This should be really simple. I am using SSMS 2008, trying to get a sum of just one column. Problem is that I c开发者_开发问答urrently group on this one column and also use a HAVING statement. How do I get sum of total # of records > 1? This is my T-SQL logic currently:

select count(*) as consumer_count from #consumer_initiations
group by consumer
having count(1) > 1

But this data looks like:

consumer_count 
----------------
2
2
4
3
...

---

Wrap it?

SELECT SUM(consumer_count)
FROM (
    select count(*) as consumer_count from #consumer_initiations
    group by consumer
    having count(1) > 1
) AS whatever

---

With a nested query:

select sum(consumer_count)
FROM (

    select count(*) as consumer_count from #consumer_initiations
    group by consumer
    having count(1) > 1
) as child

---

select sum(t.consumer_count)
    from (select count(*) as consumer_count 
              from #consumer_initiations
              group by consumer
              having count(1) > 1) t

---

Try:

select sum(t.consumer_count) from
(
    select count(*) as consumer_count from #consumer_initiations
    group by consumer
    having count(1) > 1
) t

This will give you the sum of records that your original query returns. These type of queries are called nested queries.

---

Besides the wrapping in another query, you could use this:

SELECT COUNT(*) AS consumer_count 
FROM #consumer_initiations AS a
WHERE EXISTS
      ( SELECT *
        FROM #consumer_initiations AS b
        WHERE b.consumer = a.consumer
          AND b.PK <> a.PK                -- the Primary Key of the table
      )