CODEWITHSUNDEEP
t-sqlsumhaving
salvationishere
salvationishere

Reputation: 3511

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 currently 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
...

Upvotes: 0

Views: 162

Answers (5)

ypercubeᵀᴹ
ypercubeᵀᴹ

Reputation: 115530

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
      )

Upvotes: 0

Filip Popović
Filip Popović

Reputation: 2655

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.

Upvotes: 0

Joe Stefanelli
Joe Stefanelli

Reputation: 135809

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

Upvotes: 1

Marc B
Marc B

Reputation: 360642

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

Upvotes: 1

Cade Roux
Cade Roux

Reputation: 89661

Wrap it?

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

Upvotes: 3

Related Questions