Toad World® Forums

Can I do a Count Clause from within a CASE WHEN, THEN Clause

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.