CODEWITHSUNDEEP
sqlsql-serverdatabasesql-server-2008
dido
dido

Reputation: 3407

SQL divide and group by id

I have the following query below. I'm trying to pull a count of records with specific criteria then divide by the total number of records grouped by cstmr_id. However I'm getting an error. Any help would be appreciated. Also, this statement is a subquery and part of a larger select query. I'm using SQL Server 2005

Getting a "Incorrect syntax near '/'" error

Statement:

((SELECT count(*) FROM cstmr WHERE active=1 AND cstmr_type LIKE '%dtr%' 
GROUP BY cstmr_id) 
/  --division sign here. dividing top query by bottom
(SELECT count(*) FROM cstmr WHERE cstmr_type LIKE '%dtr%'
GROUP BY cstmr_id) ) As cstmr_rate

Sample Data In cstmr table:

cstmr_id    cstmr_type    active
3423        dtr           1
1236        dtr           1
1842        dtr           1
8273        sys           2
9384        aod           1
3847        sys           2

Sample Expected Result:

cstmr_id    cstmr_rate
3423        88.98
1236        25.21
1842        58.01

Basic Pseudocode

Select only active customers that are of type "dtr" and then divide by the total number of customers. Then display this derived ratio for each individual customer. This is a very basic equation and uses the same table "cstr"

Upvotes: 0

Views: 5209

Answers (4)

Jim
Jim

Reputation: 3508

It's likely not working because those two queries are returning more than one record. SQL Server can't divide a result set by a result set.

Try using joins to pull those counts instead.

EDIT

Something like this:

SELECT 
    c.cstmr_id,
    c1/c2 AS 'cstmr_rate'
FROM cstmr as c
JOIN (
    SELECT cstmr_id, count(*) AS 'c1'
    FROM cstmr 
    WHERE active=1 
    AND cstmr_type LIKE '%dtr%' 
    GROUP BY cstmr_id
    ) AS sub1 ON c.cstmr_id = sub1.cstmr_id
JOIN (
    SELECT cstmr_id, count(*) AS 'c2'
    FROM cstmr 
    WHERE cstmr_type LIKE '%dtr%'
    GROUP BY cstmr_id
    ) AS sub2 ON c.cstmr_id = sub2.cstmr_id

EDIT2

This would probably work too, assuming that active is either a 1 or 0:

SELECT
    cstmr_id,
    SUM(Active)/COUNT(*)  AS 'cstmr_rate'
FROM cstmr
GROUP BY cstmr_id

Upvotes: 1

Aaron Bertrand
Aaron Bertrand

Reputation: 280431

;WITH x AS 
(
  SELECT cstmr_id, active, c = COUNT(*) 
   FROM dbo.cstmr WHERE cstmr_type LIKE '%dtr%'
   GROUP BY cstmr_id, active
), 
cr(cstmr_id, cstmr_rate) AS
(
  SELECT cstmr_id, 
   SUM(CASE active WHEN 1 THEN c ELSE 0 END)*1.0 / SUM(c) 
  FROM x GROUP BY cstmr_id
)
SELECT cr.cstmr_id, cr.cstmr_rate --, other columns
FROM cr
--INNER JOIN -- other tables from your larger query

Upvotes: 3

Gordon Linoff
Gordon Linoff

Reputation: 1270431

Apart from your syntax problems, there are much easier ways to express what you want:

select count(distinct case when active = 1 then cstmr_id end)*1.0 / count(distinct cstmr_id)
from cstmr
where cstmr_type like '%dtr%'

If cstmr_id is not repeated in the cstmr table, you can further simplify this as:

select sum(case when active = 1 then 1.0 else 0.0 end) / count(*)
from cstmr
where cstmr_type like '%dtr%'

Or even:

select avg(active*1.0)
from cstmr
where cstmr_type like '%dtr%'

Note that I've also converted the integers to floating point. As you've written it, the values it produces are either 0 or 1, because SQL Server does integer arithmetic on integers.

Upvotes: 1

Taryn
Taryn

Reputation: 247810

It looks like you are missing an outer SELECT:

select -- You are missing this
(
    (SELECT cast(count(*) as decimal(10,2))
    FROM cstmr 
    WHERE active=1 AND cstmr_type LIKE '%dtr%' 
    GROUP BY cstmr_id) 
/  --division sign here. dividing top query by bottom
    (SELECT cast(count(*) as decimal(10,2))
    FROM cstmr 
    WHERE cstmr_type LIKE '%dtr%'
    GROUP BY cstmr_id) 
) As cstmr_rate

Upvotes: 2

Related Questions