sam2929
asked on
Report
Hi,
I have data like below:
Modlue. Status. Count
A. Hi 4
A. Hello. 5
A. Bye. 6
B. Bye. 7
K. Hi. 5
G. Hello. 5
G. Bye. 5
Want I want is count on module Aand status A and G
Like below:
A. 15
Hello 5
G. 10
I have data like below:
Modlue. Status. Count
A. Hi 4
A. Hello. 5
A. Bye. 6
B. Bye. 7
K. Hi. 5
G. Hello. 5
G. Bye. 5
Want I want is count on module Aand status A and G
Like below:
A. 15
Hello 5
G. 10
ASKER
Hello count 5 is status for module A I want that count too
still dont get it... there are 3 status in table for module A
ASKER
Yes I just want to display hello for module A and count against hello is 5
ASKER
above query give me count of module not counts add up
why you only want "A hello" not "G Hello"?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
i go with this approach:
declare @tbl table (
Module varchar(10),
Status varchar(30),
Count int
)
insert into @tbl
values
('A.', 'Hi', 4),
('A.', 'Hello.', 5),
('A.', 'Bye.', 6),
('B.', 'Bye.', 7),
('K.', 'Hi.', 5),
('G.', 'Hello.', 5),
('G.', 'Bye.', 5)
;
with cte as
(
select module, row_number() over (order by (select 1)) idx
from @tbl
group by module
)
, cte2 as
(
select 1 sn, module, Sum(count) cnt from @tbl where module in ('A.','G.') group by module
union
select 2 sn, Status, Sum(count) cnt from @tbl where module= 'A.' and status='Hello.' group by Status
)
select cte2.module, cte2.cnt
from cte2
left join cte
on cte2.module = cte.module
order by case when cte.idx is null then 2 else cte.idx end, cte2.sn
A. 15
G. 10
I dont get how you got "Hello 10"