CODEWITHSUNDEEP
sqloracle-databasegroup-bydistinct
Madmartigan
Madmartigan

Reputation: 453

Oracle SQL count group by day distinct in day and days before

I need help with this: I have this table:

DAY     WO
 1     1000
 1     1009
 1     2056
 2     1003
 2     1006
 2     1000
 3     1009
 3     6787
 4     7000
 4     7020

I need to group by day distinct WO, but not only in the day I'm grouping, also distinct in the days before, to obtain this:

DAY    WO
 1      3 (1000,1009,2056)
 2      2 (1003,1006)
 3      1 (6787)
 4      2 (7000,7020)

Thanks for your help

Sample 2:
DAY   WO
6   379157
9   379157
10  379404
10  379362
19  386118
24  386547
25  388711
25  386547
30  386547

Upvotes: 2

Views: 5448

Answers (4)

markusk
markusk

Reputation: 6667

Use row_number to enumerate work order occurrences, then filter out all rows except the first for each work order and count what's left.

SELECT day, count(*) wo
FROM (SELECT day, row_number() OVER (PARTITION BY wo ORDER BY day) rn
      FROM day_wo) t
WHERE rn = 1
GROUP BY day
ORDER BY day;

Upvotes: 0

Giorgos Betsos
Giorgos Betsos

Reputation: 72165

You can use the following query:

SELECT DAY, COUNT(DISTINCT WO)
FROM (
   SELECT MIN(DAY) AS DAY, WO
   FROM mytable 
   GROUP BY WO) AS t
GROUP BY DAY

Demo here

Explanation: The inner query delivers the first Day related to each WO value (if by first you mean the minimum Day value. The outer query can then perform a COUNT DISTINCT operation to get the number of distinct WO values per DAY.

Edit: To get all days returned, even those having no WO, you can use the following query:

SELECT t1.DAY, 
       COUNT(DISTINCT t2.WO) wo_cnt,
       LISTAGG(t2.WO, ',') WITHIN GROUP (ORDER BY t2.WO) wo_list
FROM (
  SELECT DISTINCT DAY
  FROM mytable) t1
LEFT JOIN (
   SELECT MIN(DAY) AS DAY, WO
   FROM mytable 
   GROUP BY WO
) t2 ON t1.DAY = t2.DAY
GROUP BY t1.DAY
ORDER BY t1.DAY

Demo here

Upvotes: 3

Regressor
Regressor

Reputation: 1973

select a.day_number, count(a.WO) as WO_count
from (
SELECT * FROM data d 
WHERE d.day_number = ( 
SELECT MIN(day_number) FROM data d2
WHERE d.WO = d2.WO

) 
) as a
group by a.day_number

Upvotes: 0

gmiley
gmiley

Reputation: 6604

You want to use LISTAGG to concatenate the values together, plus a little extra string concatenation for formatting:

-- START: table data mockup
with days as
(
    select 1 as "day", 1000 as "wo" from dual
    union all
    select 1 as "day", 1009 as "wo" from dual
    union all
    select 1 as "day", 2056 as "wo" from dual
    union all
    select 2 as "day", 1003 as "wo" from dual
    union all
    select 2 as "day", 1006 as "wo" from dual
    union all
    select 2 as "day", 1000 as "wo" from dual
    union all
    select 3 as "day", 1009 as "wo" from dual
    union all
    select 3 as "day", 6787 as "wo" from dual
    union all
    select 4 as "day", 7000 as "wo" from dual
    union all
    select 4 as "day", 7020 as "wo" from dual
)
-- END: table data mockup

select "day", count("wo") || ' (' || listagg("wo", ',') within group (order by "wo") || ')' "WO"
from days d1
where d1."wo" not in
(
    select d2."wo"
    from days d2
    where d2."day" < d1."day"
)
group by "day";

Upvotes: 0

Related Questions