This is my first time submitting a question so please let me know if I break in Forum Rules here. Here is my existing (working) script. I’ve placed a note “INSERT OPTION HERE” as a placeholder for the coding I am asking for help with.
select trn.wh_id
, to_char(trn.end_tran_date, ‘MM/DD/YYYY’) AS DATE_
, count(CASE WHEN trn.tran_type in (‘303’,‘304’,‘305’) then ‘1’ end ) as Priority_pick
– INSERT OPTION HERE.
, count(CASE WHEN trn.tran_type = ‘301’ then ‘3’ end ) as WHSE_Pick
, count(CASE WHEN trn.TRAN_TYPE in (‘151’,‘161’) then ‘4’ end ) as Receipts
, count(CASE WHEN trn.tran_type = ‘151’ then ‘5’ end ) as Stage_PO_REC
, count(CASE WHEN trn.tran_type = ‘161’ then ‘6’ end ) as ASN_REC
, count(CASE WHEN trn.tran_type in (‘202’,‘252’,‘256’) then ‘7’ end ) as Puts
, count(CASE WHEN trn.tran_type = ‘256’ then ‘8’ end ) as Putaway
, count(CASE WHEN trn.tran_type = ‘252’ then ‘9’ end ) as Replen
, count(CASE WHEN trn.tran_type = ‘800’ then ‘10’ end ) as CYCC
, count(CASE WHEN trn.tran_type = ‘885’ then ‘11’ end ) as Audits
, sum(case when trn.tran_type in (‘303’,‘304’,‘305’,‘301’,‘151’,‘161’,‘202’,‘252’,‘256’,‘800’,‘885’) then 1 end) ttl_trans
from t_tran_log trn
where trn.wh_id = :whid
and trn.end_tran_time >sysdate -:days
group by trn.wh_id,to_char(end_tran_date, ‘MM/DD/YYYY’)
order by date_;
Data is pulling from a transaction log. In our world, I have a line count (the number of items on a ticket and a Pick Count (the number of pulls required to fulfill the request line count). I’m looking to get a LINE COUNT total. The table has a LINE_NUMBER COLUMN which could be summed to provide the required data point but I cannot figure out how to write that command.
Hi Ryan and welcome to the community!
Have you tried simple count(line_number)? Or is it giving a wrong output? If it does not help please send a sample of data (top 100 rows) from t_tran_log for us to better understand the data context. Not sure whether you are able to add attachments but feel free to send it to my email: martin.holkovic@quest.com
Just a guess - the name “line_number” is rather an ID which should not be sum’d but count’d instead…
Had tried Count(Line_Number) As WHSE_LINE_COUNT and got this.
WH_ID
DATE_
WHSE_LINE
WHSE_PICK
RECEIPTS
STAGE_PO_REC
ASN_REC
PUTS
PUTAWAY
CYCC
AUDITS
REPLEN
TTL_TRANS
1
03/21/2016
11736
1835
919
462
457
1135
997
431
294
82
6515
Should have been this:
3/21/16
1,727
1,835
I’ve attached the raw Data. Looking to get the LINE_COUNT for all tran_type ‘301’ data points.
Thank you and have a wonderful day,
Ryan A. Jones
Branch Configuration Analyst C:443-801-1974 Direct F: 443-543-2244
“In this world of give and take, there are not enough willing to give what it takes.” Lt. Clebe McLary Vietnam*
Purple Heart*
www.ferguson.com
www.mirabelleproducts.com
www.monogrambrass.com
www.proflo.com
[](http://www.facebook.com/FergusonShowrooms)
From: Martin.Holkovic [mailto:bounce-MartinHolkovic@toadworld.com]
Sent: Tuesday, March 22, 2016 3:39 AM
To: toaddatapoint@toadworld.com
Subject: RE: [Toad Data Point - Discussion Forum] Can I do a Count Clause from within a CASE WHEN, THEN Clause
RE: Can I do a Count Clause from within a CASE WHEN, THEN Clause
Reply by Martin.Holkovic
Hi Ryan and welcome to the community!
Have you tried simple count(line_number)? Or is it giving a wrong output? If it does not help please send a sample of data (top 100 rows) from t_tran_log for us to better understand the data context. Not sure whether you are able to add attachments but feel free to send it to my email: martin.holkovic@quest.com
Just a guess - the name “line_number” is rather a ID which should not be sum()-ed but count()-ed instead…
To reply, please reply-all to this email.
Stop receiving emails on this subject.
Or
Unsubscribe from Toad Data Point - General notifications altogether.
Toad Data Point - Discussion Forum
Flag
this post as spam/abuse.
Thanks,
“Looking to get the LINE_COUNT for all tran_type ‘301’ data points.” - but isn’t this requirement already implemented in the field WHSE_PICK?
TRAN_TYPE
DESCRIPTION
Count of LINE_NUMBER
301
Pick No Inv
10
Pick No Inv - Override
1
Picking (pick)
1756
Picking (pick) - Override
68
Grand Total
1835
If not then I am not clear in the requirement… Are you able to filter the records in excel and show me what formula/conditions you have applied (just to simulate the desired count() aggregate function that you need)
Was hoping to get it included in a automated process that spits out this.
WH_ID
DATE
PRIORITY_PICK
LINE_COUNT
WHSE_PICK
RECEIPTS
STAGE_PO_REC
ASN_REC
PUTS
PUTAWAY
CYCC
AUDITS
REPLEN
TTL_TRANS
1
03/21/2016
1901
11736
1835
919
462
457
1135
997
431
294
82
6515
Thank you and have a wonderful day,
Ryan A. Jones
Branch Configuration Analyst C:443-801-1974 Direct F: 443-543-2244
“In this world of give and take, there are not enough willing to give what it takes.” Lt. Clebe McLary Vietnam*
Purple Heart*
www.ferguson.com
www.mirabelleproducts.com
www.monogrambrass.com
www.proflo.com
[](http://www.facebook.com/FergusonShowrooms)
From: Martin.Holkovic [mailto:bounce-MartinHolkovic@toadworld.com]
Sent: Tuesday, March 22, 2016 8:55 AM
To: toaddatapoint@toadworld.com
Subject: RE: [Toad Data Point - Discussion Forum] Can I do a Count Clause from within a CASE WHEN, THEN Clause
RE: Can I do a Count Clause from within a CASE WHEN, THEN Clause
Reply by Martin.Holkovic
Thanks,
“Looking to get the LINE_COUNT for all tran_type ‘301’ data points.” - but isn’t this requirement already implemented in the field WHSE_PICK?
TRAN_TYPE
DESCRIPTION
Count of LINE_NUMBER
301
Pick No Inv
10
Pick No Inv - Override
1
Picking (pick)
1756
Picking (pick) - Override
68
Grand Total
1835
If not then I am not clear in the requirement… Are you able to filter the records in excel and show me what formula/conditions you have applied (just to simulate the desired count() aggregate function that you need)
TRAN_TYPE
DESCRIPTION
Count of LINE_NUMBER
301
Pick No Inv
10
Pick No Inv - Override
1
Picking (pick)
1756
Picking (pick) - Override
68
To reply, please reply-all to this email.
Stop receiving emails on this subject.
Or
Unsubscribe from Toad Data Point - General notifications altogether.
Toad Data Point - Discussion Forum
Flag
this post as spam/abuse.
Sorry but I am confused now - you wrote “Was hoping to get it included in a automated process that spits out this.” — So the count(line_count) is the correct solution? As I am not the user of your data I cannot evaluate this.
Before you wrote this:
Should have been this:
3/21/16
1,727
1,835
So the 1727 is the correct amount that you expect in the result? If yes then how did you calculate the 1727 number?
Because based on the screenshot from excel that I have sent earlier i confirm that count(line_count) limited to tran_type = 301 is exactly 1835… But not sure how to get 1727 on paper… Please clarify the formula that you need help with and I might be able to assist.
These two are obviously wrong (based on what you say):
- count(line_count)
- count(CASE WHEN trn.tran_type = ‘301’ then ‘3’ end)
Thanks
I would have expected something more like:
Sum(Case When someField = ‘someValue’ Then 1 Else 0 End) as TotalCount for that value
Hope this helps,
sum(CASE WHEN trn.tran_type = ‘301’ then 1 else 0 end ) will give you the total records for all ‘301’ transactions.