CODEWITHSUNDEEP
sqlsql-servercalculated-field
John_Rodgers
John_Rodgers

Reputation: 181

SQL Server - Calculate total and filtered value

I've the following dataset:

Machine Type Value
1        A  11
1        B  32
2        A  23
3        A  1
4        B  23
4        B  31
5        B  56
6        A  12

And I want to the following calculation:

SELECT COUNT(WHERE TYPE = A) / COUNT(TOTAL)
FROM....

Which is the best way to do it? Is using With like:

DECLARE @CNT INT
SET @CNT = (SELECT COUNT(*) FROM dataset)
SELECT COUNT(*)/CNT
FROM dataset
WHERE TYPE = A

But if I have a big query repeating the same query for this calculations puts SQL to slow... Anyone can give a better solution?

Upvotes: 0

Views: 342

Answers (4)

jarlh
jarlh

Reputation: 44795

Use a case expression to do conditional counting.

(When type <> a, the case will return null. count() doesn't count nulls.)

SELECT COUNT(case when TYPE = A then 1 end) * 1.0 / COUNT(*)
FROM dataset

EDIT:

Inspired by the other answers I decided to run some performance tests. The table tx used here has 10's of millions of rows. The column c2 is indexed, and has some hundred different values randomly placed all over the table.

Query 1:

select count(case when c2 = 'A' then 1 end) * 1.0 / count(*) from tx;

Direct I/O count      :      83067
Buffered I/O count    :          0
Page faults           :          3
CPU time     (seconds):      77.18
Elapsed time (seconds):      77.17

Query 2:

select avg(case when c2 = 'A' then 1.0 else 0.0 end) from tx;

Direct I/O count      :      83067
Buffered I/O count    :          0
Page faults           :          0
CPU time     (seconds):      84.90
Elapsed time (seconds):      84.90

Query 3:

select (select count(*) from tx where c2 = 'A') * 1.0 /
          (select count(*) from tx)
from onerow_table;

Direct I/O count      :      86204
Buffered I/O count    :          0
Page faults           :          2
CPU time     (seconds):       3.45
Elapsed time (seconds):       3.45

PS. Not run on MS SQL Server.

Upvotes: 2

John Cappelletti
John Cappelletti

Reputation: 82010

Here is a little trick that Gordon demonstrated a couple of weeks ago. (can't seem to find the link)

Declare @YourTable table (Machine int, Type char(1), Value int)
insert into @YourTable values
 (1,'A',11)
,(1,'B',32)
,(2,'A',23)
,(3,'A',1 )
,(4,'B',23)
,(4,'B',31)
,(5,'B',56)
,(6,'A',12)

select avg(case when type='a' then 1.0 else 0 end)
from @YourTable

Returns

0.500000

Upvotes: 1

LONG
LONG

Reputation: 4620

select 
cast((sum(case when Type='A' then 1 else 0 end)) as float)/cast(Count(Type)) as float)
from dataset

Upvotes: 0

SqlZim
SqlZim

Reputation: 38063

using conditional aggregation: summing 1.0 will give you a percentage that isn't converted to an int of 0 or 1.

select sum(case when type='a' then 1.0 else 0 end)/count(*)
from t

test setup: http://rextester.com/GXN95560

create table t (Machine int, Type char(1), Value int)
insert into t values
 (1,'A',11)
,(1,'B',32)
,(2,'A',23)
,(3,'A',1 )
,(4,'B',23)
,(4,'B',31)
,(5,'B',56)
,(6,'A',12)

select sum(case when type='a' then 1.0 else 0 end)/count(*)
from t

returns: 0.500000

SELECT COUNT(case when TYPE = 'a' then 1 end) / COUNT(*)
FROM t

returns: 0

Upvotes: 1

Related Questions