CODEWITHSUNDEEP
sqlsql-server
coburne
coburne

Reputation: 149

Dividing a single number by count of rows in each grouping

I have 10 groups total. The grouping numbers (Column A) restart at 1 at every distinct value in column Type. I want to divide 100 by the 10 total groups across all types, then the resulting figure per group by number of entries(rows) in each grouping.

Column A + Type is my group identifier, column B is what I want the final result to look like.

I tried some variations equations based off of nesting queries with counts grouped by A and type, but I'm not getting my head around how to pull it all together. 

ID  A   B   TYPE

01  1   10  APPLE
02  2   5   APPLE
03  2   5   APPLE
04  3   2.5 APPLE
05  3   2.5 APPLE
06  3   2.5 APPLE
07  3   2.5 APPLE
08  4   5   APPLE
09  4   5   APPLE
10  5   10  APPLE
11  1   5   ORANGE
12  1   5   ORANGE
13  2   10  ORANGE
14  3   5   ORANGE
15  3   5   ORANGE
16  4   2.5 ORANGE
17  4   2.5 ORANGE
18  4   2.5 ORANGE
19  4   2.5 ORANGE
20  5   10  ORANGE

Upvotes: 0

Views: 5255

Answers (3)

Juan Carlos Oropeza
Juan Carlos Oropeza

Reputation: 48197

EDITED

To fix aditional row

21  5   **  ORANGE

SQL Fiddle Demo

WITH subGroup as (
   SELECT [TYPE], [A], count([TYPE]) as total
   FROM Table1
   GROUP BY [TYPE], [A]
),
totalGroup as (
    SELECT count(DISTINCT [TYPE] + CAST([A] as varchar(10))) as total
    FROM Table1        
)
SELECT t.ID, t.[A], (100.0 / tg.total / s.total) as [B], t.[TYPE]
FROM Table1 t
inner join subGroup s
  on t.[Type] = s.[Type]
 and t.[A] = s.[A]
, totalGroup tg

OUTPUT

| ID | A |   B |   TYPE |
|----|---|-----|--------|
|  1 | 1 |  10 |  APPLE |
|  2 | 2 |   5 |  APPLE |
|  3 | 2 |   5 |  APPLE |
|  4 | 3 | 2.5 |  APPLE |
|  5 | 3 | 2.5 |  APPLE |
|  6 | 3 | 2.5 |  APPLE |
|  7 | 3 | 2.5 |  APPLE |
|  8 | 4 |   5 |  APPLE |
|  9 | 4 |   5 |  APPLE |
| 10 | 5 |  10 |  APPLE |
| 11 | 1 |   5 | ORANGE |
| 12 | 1 |   5 | ORANGE |
| 13 | 2 |  10 | ORANGE |
| 14 | 3 |   5 | ORANGE |
| 15 | 3 |   5 | ORANGE |
| 16 | 4 | 2.5 | ORANGE |
| 17 | 4 | 2.5 | ORANGE |
| 18 | 4 | 2.5 | ORANGE |
| 19 | 4 | 2.5 | ORANGE |
| 20 | 5 |   5 | ORANGE |
| 21 | 5 |   5 | ORANGE |

Upvotes: 2

Kar
Kar

Reputation: 505

Hope this query helps

Select At.A, B = CAST(tot as float) / Cnt, At.Type 
From Test AT
Inner Join 
    (Select A, Type, Cnt = Count(*) From Test
    Group By A, Type) BT On AT.A = BT.A and AT.Type = BT.Type
Inner Join 
    (Select Type, Tot = Count(A) From Test
    Group By Type) CT On CT.Type = AT.Type`

Upvotes: 1

JNevill
JNevill

Reputation: 50200

You can use Windowing functions to do this, if your SQL Server is 2008+:

SELECT
    ID,
    A,
    (COUNT(*) OVER (PARTITION BY TYPE))/(COUNT(*) OVER (PARTITION BY A,TYPE)) as B,
    TYPE
FROM <table>
order by TYPE, A asc

Editing to add: I don't really understand the "Divide 100 by..." part but the math here matches your desired results... so I assume it works out the same, right?

Upvotes: 1

Related Questions