-
How to query another table from other table result
Dear Sir,
I have 2 table. 1 table is keeping all category and another table is the detail of problem including category
My category table as below
Access
Hardware
Network
My detail problem table as below
1 John Access.Missing
2 Peter Access.NoId
3 John Hardware.Failure
4 John Hardware.Missing
5 Peter Network.Cable
6 John Network.Hang
I would like to the count of each category & the result as below
Access 2
Hardware 2
Network 2
Please help me how I can get result as above.
Thanks
-
What are the column names in detail table?. It looks like you have the description of category in detail table as well.
select desc, count(*)
from detail
group by desc
assuming desc is the column name for the problem each user reported.
-
On which rdbms? Do you have table schema?
-
create table a (id int, name varchar(50), status varchar(200))
insert into a select 1, 'John', 'Access.Missing'
insert into a select 2,'Peter', 'Access.NoId'
insert into a select 3,'John', 'Hardware.Failure'
insert into a select 4,'John', 'Hardware.Missing'
insert into a select 5,'Peter', 'Network.Cable'
insert into a select 6,'John', 'Network.Hang'
insert into a select 7,'jane', 'Firewall.Hang'
insert into a select 8,'Hong', 'opticFibre.cable'
insert into a select 9,'MAK', 'Telephone.Hang'
create table c (category varchar(100))
insert into c select 'Access'
insert into c select 'Hardware'
insert into c select 'Network'
select Category,count(left(status,charindex('.',status)-1) ) as StatusCount from
a join c on left(status,charindex('.',status)-1)=c.category group by category
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
|