CODEWITHSUNDEEP
sql
XJZ
XJZ

Reputation: 47

Join without on sql

For example, I have a table T1 that has two columns A and B. I want to write a sql query that get the result of COUNT(A)/COUNT(DISTINCT C), column C is a new column derived from column B. 

┌┄┄┄┄┄┬┄┄┄┄┄┬       ┄┄┄┄┐
┆  A  ┆  B  ┆         C ┆
├┄┄┄┄┄┆┄┄┄┄┄┆       ┄┄┄┄┤
┆  1  ┆  1  ┆        1b ┆
├┄┄┄┄┄┆┄┄┄┄┄┆       ┄┄┄┄┆
┆  2  ┆  2  ┆        2b ┆
├┄┄┄┄┄┆┄┄┄┄┄┆       ┄┄┄┄┤
┆  3  ┆  2  ┆        2b ┆
└┄┄┄┄┄┴┄┄┄┄┄┴       ┄┄┄┄┘

Here is what I think:

SELECT COUNT(A)/COUNT(DISTINCT C)

FROM T1 (?) (SELECT CAST(B, varchar)+'b' AS C FROM T1)

I haven't found a type of JOIN that can combine T1 and column C into a table like below:

┌┄┄┄┄┄┬┄┄┄┄┄┬┄┄┄┄┄┐
┆  A  ┆  B  ┆  C  ┆
├┄┄┄┄┄┆┄┄┄┄┄┆┄┄┄┄┄┤
┆  1  ┆  1  ┆  1b ┆
├┄┄┄┄┄┆┄┄┄┄┄┆┄┄┄┄┄┆
┆  2  ┆  2  ┆  2b ┆
├┄┄┄┄┄┆┄┄┄┄┄┆┄┄┄┄┄┤
┆  3  ┆  2  ┆  2b ┆
└┄┄┄┄┄┴┄┄┄┄┄┴┄┄┄┄┄┘

What should I do? Thanks!

Upvotes: 0

Views: 55

Answers (2)

Alexey S. Larionov
Alexey S. Larionov

Reputation: 7937

Like that? 

SELECT COUNT(A) * 1.0 / COUNT(DISTINCT C)
FROM (SELECT A, CAST(B, varchar)+'b' AS C FROM T1)

The inner query gets for each row a value of A and a modified value of corresponding B

Inner:
┌┄┄┄┄┄┬┄┄┄┄┄┬
┆  A  ┆  C  ┆
├┄┄┄┄┄┆┄┄┄┄┄┆
┆  1  ┆  1b ┆
├┄┄┄┄┄┆┄┄┄┄┄┆
┆  2  ┆  2b ┆
├┄┄┄┄┄┆┄┄┄┄┄┆
┆  3  ┆  2b ┆
└┄┄┄┄┄┴┄┄┄┄┄┴

The outer query finds what you're looking for (using the mentioned by Gordon decimal trick)

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1270513

You seem to be describing:

SELECT COUNT(A) * 1.0 / COUNT(DISTINCT C)
FROM T1

From what I can tell, your database does integer division, so 3/2 = 1 rather than 1.5. The * 1.0 turns the integers into decimals so the truncation does not happen.

Upvotes: 1

Related Questions