Adding SQL Sum to query

Question

I'm trying to add a SQL Sum to display the total number of IN_QTY and the total number loaded. Example: In the screenshot, I have 200 for IN_QTY, and based on LOGIN_DTTM I have 75 loaded.

I would like to have it displayed like this 200/75.

Can someone help with this request? It's my first time using Stack overflow.

My Query:

Select a.lot, a.lpt, a.opn, a.in_Qty,  a.device, a.arrival_Dttm, a.login_dttm, a.priority, a.location
From
(Select  lma.lot, lma.lpt, lma.opn, lma.device, lma.in_qty, arrival_dttm, login_dttm, lco.equip_grp, ls.priority, ls.lot_code3 as location
From lot_cur_opn lco, lot_move_age lma, dm_device_attributes dda, lot_str ls
where lco.facility = lma.facility
and dda.facility = lma.facility
and lma.facility = ls.facility
and dda.device = lma.device
and lco.lot = lma.lot
and lma.lot = ls.lot
and lma.facility = 'DP1DM5'
and lco.opn in ('4927')  --specify query operation
and lma.departure_dttm is null
and lma.latest = 'O'
and ls.latest = 'Y'
and dda.family not like '%PILOT%'
and dda.family not like '%NONE%'
and dda.family not like '%ENG%'
and dda.family not like '%LBQ%'
) a

Join
(Select equip_grp, substr(trk_id,0,3)
From equip_grp_trk_lst egl
where egl.stop_dttm is null
and egl.status = 'A'
and egl.trk_id like 'SE2%'  --specify equipment type
and egl.trk_id not like '%LOGTHR%'
group by equip_grp, substr(trk_id,0,3)) b
On b.equip_grp = a.equip_grp
order by priority, arrival_dttm

Screenshot: Current Output

your existing query returns multiple rows. Not sure how you are expecting to return a scalar value unless you do a count(*). Are you saying you don't want to see all those rows as you show in the current output, and JUST 200/75? — OldProgrammer
– OldProgrammer, Commented Jun 16, 2022 at 20:26
@OldProgrammer yes just want to show 200/75. I meant to say Sum and not count. I apologize… still very new to coding. — MEDR
– MEDR, Commented Jun 16, 2022 at 20:32
I added all the rows to show how the table was formed. Wanted to show a picture example. — MEDR
– MEDR, Commented Jun 16, 2022 at 20:41

OldProgrammer · Accepted Answer · 2022-06-16 20:42:51Z

1

try something like this:

select sum(in_qty), 
       sum(case when login_dttm is not null then in_qty end)
       from 
(
  ... your big query ...
)

this will sum up all the in_qty and only include those that have a value that is not null for login_dttm as a separate sum.

answered Jun 16, 2022 at 20:42

OldProgrammer

12.2k4 gold badges27 silver badges52 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Adding SQL Sum to query

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related