Link to home
Start Free TrialLog in
Avatar of sam2929
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
Avatar of HainKurt
HainKurt
Flag of Canada image

select modlue, count(1) from myTable where modlue in ('A','G') group by modlue
A.  15
G.  10

I dont get how you got "Hello 10"
Avatar of sam2929
sam2929

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
Avatar of sam2929

ASKER

Yes I just want to display hello for module A and count against hello is 5
Avatar of sam2929

ASKER

above query give me count of module not counts add up
why you only want "A hello" not "G Hello"?
ASKER CERTIFIED SOLUTION
Avatar of HainKurt
HainKurt
Flag of Canada image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Ryan Chong
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

Open in new window