CODEWITHSUNDEEP
sqlsql-servert-sql
Tamil
Tamil

Reputation: 77

Divide the count of two columns in SQL

I am trying to count two columns with 2nd column as distinct values. The 2nd column data can have multiple values in the first column. So i would like to count the 1st column and distinct count of second column and divide the 1st by the 2nd column to get the o/p. Now there is a third column on which we need to group the data.

Example:

A   B   C
----------------
30  10  tomatoes
30  10  tomatoes
 5  10  tomatoes
20   5  Potatoes
20   5  Potatoes
40   5  Potatoes
10  15  Onions
40  15  Onions
20  15  Onions

Looking for possible solutions.

Below is a simple try. I am not sure if this is right or should i use partition by. Any help would be appreciated.

SELECT  
    C, 
    COUNT('A') AS A,  
    COUNT(DISTINCT 'B') AS B,
    ((COUNT('A')) / COUNT(DISTINCT 'B')) AS AB
FROM 
    [Table]
GROUP BY
    C
ORDER BY 
    C

Upvotes: 1

Views: 2453

Answers (2)

Sean Lange
Sean Lange

Reputation: 33581

Be careful here doing division. When you have count / count you have integer math. So something like 3/2 will result in 1, not 1.5. I modified your sample data a little bit to demonstrate what I mean. I included both calculations in the output so you can the difference.

declare @Something table
(
    A int
    , B int
    , C varchar(20)
)
insert @Something values
(30, 10, 'tomatoes')
, (30, 11, 'tomatoes')
, (5 , 10, 'tomatoes')
, (20, 5 , 'Potatoes')
, (20, 5 , 'Potatoes')
, (40, 5 , 'Potatoes')
, (10, 15, 'Onions')
, (40, 15, 'Onions')
, (20, 15, 'Onions')

select count(A)
    , count(distinct B)
    , count(A) / (count(distinct B) * 1.0) --multiplied by 1.0 to force division to use a decimal
    , count(A) / count(distinct B) --integer math
    , C
from @Something s
group by C

Upvotes: 2

Yogesh Sharma
Yogesh Sharma

Reputation: 50173

Your query logic is correct, but contains some typo's :

It would be :

SELECT C, COUNT(A) AS A, COUNT(DISTINCT B) AS B,
       COUNT(A) / COUNT(DISTINCT B) AS AB
FROM [Table]
GROUP BY C
ORDER BY C;

Your are passing constant value count('A') which would return no of rows in table rather than count of column A.

Upvotes: 0

Related Questions