Multiple Couunts & Grouping

[robyoud]

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

[robyoud]

-- 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