CODEWITHSUNDEEP
sqloracle-database
LND
LND

Reputation: 1

Join two table, count column rows on three where statements

I have a table lot_bill_of_operations with lines and table lot_bill_of_lots_serial_xref which has serial number and timestamp columns. The tables are related by primary key bol_id.

I am trying to count how many serial numbers are passed on three shifts group by line, but I have same result for each subquery.

Here is the query:

select lot_bill_of_operations.wc_line_code,

(select  count(part_sn) from lot_bill_of_lots_serial_xref
  where lot_bill_of_lots_serial_xref.time_stamp between '07-MAY-17 06:00:00'
    and '07-MAY-17 14:00:00'
  group by lot_bill_of_operations.wc_line_code)firstshift,

(select  count(part_sn) from lot_bill_of_lots_serial_xref
  where lot_bill_of_lots_serial_xref.time_stamp between '07-MAY-17 14:00:00'
    and '07-MAY-17 22:00:00'
  group by lot_bill_of_operations.wc_line_code)secondshift,

(select  count(part_sn) from lot_bill_of_lots_serial_xref
  where lot_bill_of_lots_serial_xref.time_stamp between '07-MAY-17 22:00:00'
    and '08-MAY-17 06:00:00'
  group by lot_bill_of_operations.wc_line_code)thirdshift

FROM lot_bill_of_operations inner JOIN lot_bill_of_lots_serial_xref
    ON lot_bill_of_operations.bol_id = lot_bill_of_lots_serial_xref.bol_id
group by lot_bill_of_operations.wc_line_code

And here is the results that gets:

enter image description here

What am I doing wrong?

Upvotes: 0

Views: 69

Answers (3)

Utsav
Utsav

Reputation: 8093

Without sample data, it is difficult to understand what you want, but based on your query, I believe you need something like below.

  • Also avoid comparing string for dates. Use a date conversion function like to_date/to_timestamp while comparing dates for accurate and non ambiguous results.
  • Use between while giving a range.

  • Use alias for table names for better readability.

    select o.wc_line_code
    ,count(case when 
            x.time_stamp between to_date('07-MAY-17 06:00:00','DD-MON-YY HH24:MI:SS') 
                            and  to_date('07-MAY-17 14:00:00','DD-MON-YY HH24:MI:SS')
            then 1) as firstshift
-- , similary for other shifts

FROM lot_bill_of_operations o 
    inner JOIN 
     lot_bill_of_lots_serial_xref x
    ON o.bol_id = x.bol_id
group by o.wc_line_code;

Upvotes: 1

Alex Poole
Alex Poole

Reputation: 191235

It looks like you want conditional counts, which you can get with a case expression:

select lbo.wc_line_code,
  count(case when lblsx.time_stamp between timestamp '2017-05-07 06:00:00'
    and timestamp '2017-05-07 14:00:00' then lblsx.part_sn end) as firstshift,
  count(case when lblsx.time_stamp between timestamp '2017-05-07 14:00:00'
    and timestamp '2017-05-07 22:00:00' then lblsx.part_sn end) as secondshift,
  count(case when lblsx.time_stamp between timestamp '2017-05-07 22:00:00'
    and timestamp '2017-05-08 06:00:00' then lblsx.part_sn end) as thirdshift
from lot_bill_of_operations lbo
inner join lot_bill_of_lots_serial_xref lblsx
on lbo.bol_id = lblsx.bol_id
group by lbo.wc_line_code;

I've switched to timestamp literals, as you are relying on your NLS settings to implicitly convert strings to timestamps. (Or possibly dates, it isn't clear from what you've shown).

However, using between means the periods overlap slightly, so you might really want:

select lbo.wc_line_code,
  count(case when lblsx.time_stamp >= timestamp '2017-05-07 06:00:00'
    and lblsx.time_stamp < timestamp '2017-05-07 14:00:00' then lblsx.part_sn end) as firstshift,
  count(case when lblsx.time_stamp >= timestamp '2017-05-07 14:00:00'
    and lblsx.time_stamp < timestamp '2017-05-07 22:00:00' then lblsx.part_sn end) as secondshift,
  count(case when lblsx.time_stamp >= timestamp '2017-05-07 22:00:00'
    and lblsx.time_stamp < timestamp '2017-05-08 06:00:00' then lblsx.part_sn end) as thirdshift
from lot_bill_of_operations lbo
inner join lot_bill_of_lots_serial_xref lblsx
on lbo.bol_id = lblsx.bol_id
group by lbo.wc_line_code;

You might also want to include the time_stamp range as an overall filter to reduce the amount of data inspected:

...
where lblsx.time_stamp >= timestamp '2017-05-07 06:00:00'
and lblsx.time_stamp < timestamp '2017-05-08 06:00:00'
group by lbo.wc_line_code;

If you're basing this on the current day you can use date arithmetic instead of fixed values.

Upvotes: 2

Roger Cornejo
Roger Cornejo

Reputation: 1547

Try the following:

select lot_bill_of_operations.wc_line_code
, sum (case when time_stamp between '07-MAY-17 06:00:00' and '07-MAY-17 14:00:00'
 then 1 else 0 end ) firstshift
, sum (case when time_stamp between '07-MAY-17 14:00:00' and '07-MAY-17 22:00:00'
 then 1 else 0 end ) secondshift
, sum (case when time_stamp between '07-MAY-17 22:00:00' and '08-MAY-17 06:00:00'
 then 1 else 0 end ) thirdshift
FROM lot_bill_of_operations inner JOIN lot_bill_of_lots_serial_xref
    ON lot_bill_of_operations.bol_id = lot_bill_of_lots_serial_xref.bol_id
group by lot_bill_of_operations.wc_line_code
;

Upvotes: 0

Related Questions