Finding SUM and COUNT 1

[asimeqi]

I have this table:

DECLARE @transactions TABLE (account_id INT, amount INT)

INSERT INTO @transactions VALUES (1, 1)
INSERT INTO @transactions VALUES (1, 2)
INSERT INTO @transactions VALUES (1, 3)
INSERT INTO @transactions VALUES (2, 2)
INSERT INTO @transactions VALUES (2, 2)

I want to write a query that selects the accounts, the total transaction amounts and the number of times the transaction amount was > 1.

This query:

SELECT account_id, 
	COUNT(*) AS num_high_trans,
	SUM(amount) AS total_amount
FROM @transactions
WHERE amount > 1
GROUP BY account_id
HAVING count(*) > 1

gives this result:

account_id  num_high_trans total_amount 
----------- -------------- ------------ 
1           2              5
2           2              4

This query:

SELECT account_id, 
	COUNT(*) AS num_high_trans,
	SUM(amount) AS total_amount
FROM @transactions
GROUP BY account_id
HAVING count(*) > 1

gives me this result:

account_id  num_high_trans total_amount 
----------- -------------- ------------ 
1           3              6
2           2              4

Neither gives me the right result. The result must be:

account_id  num_high_trans total_amount 
----------- -------------- ------------ 
1           2              6
2           2              4

Ideas?

 

[gmmastros]

This ???

SELECT account_id, 
	Sum(case When Amount > 1 Then 1 Else 0 End) As Num_High_Trans,
    SUM(amount) AS total_amount
FROM @transactions
GROUP BY account_id

The case statement returns a 1 or 0 depending on the conditions, so summing those will essentially be a count of records for that condition.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[SQLDenis]

Simple subquery join

DECLARE @transactions TABLE (account_id INT, amount INT)

INSERT INTO @transactions VALUES (1, 1)
INSERT INTO @transactions VALUES (1, 2)
INSERT INTO @transactions VALUES (1, 3)
INSERT INTO @transactions VALUES (2, 2)
INSERT INTO @transactions VALUES (2, 2)

SELECT t1.account_id,num_high_trans,total_amount
FROM(SELECT account_id, 
    COUNT(*) AS num_high_trans
    FROM @transactions
where amount > 1
GROUP BY account_id) t1 join
(
SELECT account_id, 
       SUM(amount) AS total_amount
FROM @transactions
GROUP BY account_id) t2 on t1.account_id=t2.account_id

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[SQLDenis]

George's answer is more elegant of course ;-)

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[gmmastros]

You get an E for effort though.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[SQLDenis]

Well that's better than an F for eFFort

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[asimeqi]

Thanks guys,
both your queries are instructional.