CODEWITHSUNDEEP
sqloracle-database
meph
meph

Reputation: 47

SQL SUM COUNT with group by's

I have a specific problem which i can't solve and i dont really know how to work it out.

I have lets say summed events for days. like the following: 

date      | id | sub id | sum(a) | sum(b) | sum(c) | 
18.01.01  | 1  |  12    |  105   | 10     | 0
18.01.01  | 2  |  11    |  100   | 3      | 7
18.01.02  | 1  |  12    |  103   | 2      | 5
18.01.03  | 3  |  7     |  143   | 7      | 9
18.01.03  | 1  |  12    |  113   | 1      | 4

what i want to find out is how much days are there for each id and sub id pair, like: 

sum(days) |date      | id | sub id | sum(a) | sum(b) | sum(c) 
 3        |18.01.01  | 1  |  12    |  105   | 10     | 0
 1        |18.01.01  | 2  |  11    |  100   | 3      | 7
 3        |18.01.02  | 1  |  12    |  103   | 2      | 5
 1        |18.01.03  | 3  |  7     |  143   | 7      | 9
 3        |18.01.03  | 1  |  12    |  113   | 1      | 4

for id = 1 there is 3 days since he has 3 records with different dates. Now i know the basics, but everytime i count the days it returns invalid values (like 106 for id = 1 ). And the dates are timestamps, im using trunc(date, 'DD') to get them for daily. I tried extract(day from date) and even sum() over() but nothing seems to work properly.

Thanks for any tips in advance. Edit: Stackoverflow wrongfully formatted the tables

Upvotes: 0

Views: 1482

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269773

You seem to want a window function:

select count(*) over (partition by id) as sum_days,
       . . .

Upvotes: 1

fauxmosapien
fauxmosapien

Reputation: 535

Adding something like

 COUNT(DISTINCT trunc(date, 'DD') ) OVER (PARTITION BY id, subID)

might work. If not, please post the statement you're using to build your first table.

Upvotes: 1

Related Questions