CODEWITHSUNDEEP
sqlsnowflake-cloud-data-platform
Chris90
Chris90

Reputation: 1998

% of specific value in column over # of rows in that column

I have output data from a query such as

ID | Type | Date
321   Car    2/2/12
443   Truck  4/4/21
444   Car    4/3/21
639   Truck  3/5/21
211   Car    3/4/21

How can I add an outer query so I can calculate grouped by the Type column the % for each value in that column?

Desired Output:

Type  |  %
Car     .6
Truck   .4

Upvotes: 0

Views: 59

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1269873

Just use aggregation and division:

select type,
       count(*) * 1.0 / sum(count(*)) over () as ratio
from t
group by type;

You don't need a subquery of any sort to do this.

Upvotes: 1

Simon D
Simon D

Reputation: 6269

You can use ratio_to_report for this as well:

select
    type,
    sum(ratio)
from (select type,
             ratio_to_report(1) over () as ratio
         from ([your subquery]))
group by type;

Returns:

+-----+----------+
|TYPE |SUM(RATIO)|
+-----+----------+
|Car  |0.600000  |
|Truck|0.400000  |
+-----+----------+

Upvotes: 0

DineshDB
DineshDB

Reputation: 6193

Try the following answer.

SELECT T.Type, (COUNT(T.Type)*1.0)/(SELECT COUNT(*) FROM TableName)[%]
FROM TableName T
GROUP BY T.Type

If it throwing any error, then please let me know.

Output:

Type    %
Car     0.600000000000
Truck   0.400000000000

You can use the subquery in the CTE.

;WITH CTE
AS
(SELECT * FROM TableName)
SELECT T.Type, (COUNT(T.Type)*1.0)/(SELECT COUNT(*) FROM CTE)[%]
FROM CTE T
GROUP BY T.Type

This will also give you the same result.

Upvotes: 1

Related Questions