Divide the count of two columns in SQL

Question

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

Single quotes are used for literal strings, double quotes are for object identifiers. COUNT(DISTINCT 'B') will always return 1, try COUNT(DISTINCT B) — HoneyBadger
– HoneyBadger, Commented Sep 5, 2018 at 14:55
I consider the misuse of quotes to be a simple typographical error. — Gordon Linoff
– Gordon Linoff, Commented Sep 5, 2018 at 15:07
Thank you! I corrected it! it worked with minor corrections as mentioned. — Tamil
– Tamil, Commented Sep 5, 2018 at 21:40

Sean Lange · Accepted Answer · 2018-09-05 15:01:37Z

2

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

answered Sep 5, 2018 at 15:01

Sean Lange

33.7k3 gold badges28 silver badges42 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Tamil Over a year ago

Yes, that looks neat. So in order to round the decimal values to 2 or 3 values, what would be the input in the query.

Sean Lange Over a year ago

Well to round it you would use the ROUND function. You could then convert it to a type with fewer decimals if you want.

Tamil Over a year ago

Thanks Sean! it helped!!

Yogesh Sharma · Accepted Answer · 2018-09-05 14:57:06Z

0

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.

answered Sep 5, 2018 at 14:57

Yogesh Sharma

50.2k5 gold badges31 silver badges53 bronze badges

Collectives™ on Stack Overflow

Divide the count of two columns in SQL

2 Answers 2

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related