Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 2 of 2
  1. #1
    New to the CF scene
    Join Date
    Jan 2012
    Posts
    2
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Post Multiple Couunts & Grouping

    Hello, I have a statement that peforms multiple counts.

    But when it returns my data, they don't appear to be grouped.

    exmaple:

    The first row returned is location 000008486, which show's a total of 16 records, this is wrong in fact there are 16 rows of NEG and a further 2 records, 1 for 'Short' and 1 for 'Skip' making a total of 18 records.

    16 Negative invn in Active
    1 Short Pick in Active (Pull)
    1 Skip for Active (Put)

    So I would like all 18 to show across 1 row and the total to be correct.

    I hope somebody can help, I'm not great ar grouping and don't even know if it can be done this way or not, any help you can offer is very much appreciated...

    select task_genrtn_ref_nbr "Location",
    (select count(*)
    from task_hdr y
    where y.task_desc = x.task_desc
    and y.task_genrtn_ref_nbr = x.task_genrtn_ref_nbr
    and y.invn_need_type = 101) as "Total",
    (select count(*)
    from task_hdr y
    where y.task_desc = x.task_desc
    and y.task_genrtn_ref_nbr = x.task_genrtn_ref_nbr
    and y.task_desc = 'System Generated Cycle Count Of Active') as "Sys Gen",
    (select count(*)
    from task_hdr y
    where y.task_desc = x.task_desc
    and y.task_genrtn_ref_nbr = x.task_genrtn_ref_nbr
    and y.task_desc = 'Short Pick in Active (Pull)') as "Short",
    (select count(*)
    from task_hdr y
    where y.task_desc = x.task_desc
    and y.task_genrtn_ref_nbr = x.task_genrtn_ref_nbr
    and y.task_desc = 'Negative invn in Active') as "Neg",
    (select count(*)
    from task_hdr y
    where y.task_desc = x.task_desc
    and y.task_genrtn_ref_nbr = x.task_genrtn_ref_nbr
    and y.task_desc = 'Skip for Active (Put)') as "Skip",
    (select count(*)
    from task_hdr y
    where y.task_desc = x.task_desc
    and y.task_genrtn_ref_nbr = x.task_genrtn_ref_nbr
    and y.task_desc = 'Cycle Count Active') as "Cycle Act"
    from task_hdr x
    where x.invn_need_type = '101'
    group by task_genrtn_ref_nbr, x.task_desc
    order by 2 desc, 2 desc

  • #2
    New to the CF scene
    Join Date
    Jan 2012
    Posts
    2
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Resolved !

    -- Cycle Count Summary by Location - Most Couunted --
    SELECT
    task_genrtn_ref_nbr "Location",
    SUM(CASE WHEN invn_need_type = '101' THEN 1 ELSE 0 END) as "Total",
    SUM(CASE WHEN task_desc = 'Negative invn in Active' THEN 1 ELSE 0 END) as "Neg",
    SUM(CASE WHEN task_desc = 'System Generated Cycle Count Of Active' THEN 1 ELSE 0 END) as "Sys Gen",
    SUM(CASE WHEN task_desc = 'Short Pick in Active (Pull)' THEN 1 ELSE 0 END) as "Short",
    SUM(CASE WHEN task_desc = 'Skip for Active (Put)' THEN 1 ELSE 0 END) as "Skip",
    SUM(CASE WHEN task_desc = 'Cycle Count Active' THEN 1 ELSE 0 END) as "Cycle Act"
    FROM task_hdr
    GROUP BY task_genrtn_ref_nbr
    ORDER BY 2 desc




  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •