SQL Query (SQL Server 2008) to retrieve data from two tables and group results
I have two tables (assume 2 columns: ID
and category
). I would like to retrieve records from first table, from second table, group results by category (there are the same categories in two tables) and count them separately. For example:
First table:
ID | category
-------------
1 | category1
2 | category2
3 | category3
4 | category1
5 | category2
Second table:
ID | category
--------------
a | category1
b | category2
c | category3
d | category3
I would like to get results like:
category | count(id from 1 table) | cou开发者_如何转开发nt(id from 2 table)
------------------------------------------------------------
category1 | 2 | 1
category2 | 2 | 2
category3 | 1 | 3
I try this:
SELECT r.AFFECTED_ITEM as usluga,
COUNT(r.ID) AS problemy,
(SELECT COUNT(k.ID)
FROM KNOWNERRORM1 k
WHERE k.AFFECTED_ITEM = r.AFFECTED_ITEM
GROUP BY k.AFFECTED_ITEM) AS znane_bledy<br>
FROM ROOTCAUSEM1 r
group by r.AFFECTED_ITEM
...but in results there are less records that it should be (because of inner join).
When I used full join there are more records that it should be..
Made the modifications as per Siva's suggestions.
SELECT COALESCE(table1Grouped.Category, table2Grouped.Category) AS Category, COALESCE(table1Grouped.IDCount, 0) AS Table1IDCount, COALESCE(table2Grouped.IDCount, 0) AS Table2IDCount
FROM
(
SELECT table1.category, COUNT(table1.ID) AS IDCount
FROM table1
GROUP BY table1.category
) AS table1Grouped
FULL OUTER JOIN
(
SELECT table2.category, COUNT(table2.ID) AS IDCount
FROM table2
GROUP BY table2.category
) AS table2Grouped
ON
table1Grouped.category = table2Grouped.Category
You could try this...
SELECT Category, COUNT(Id) AS TableOneCount, 0 AS TableTwoCount
FROM Table1
UNION
SELECT Category, 0 AS TableOneCount, COUNT(Id) AS TableTwoCount
FROM Table2
GROUP BY Category
Sorry if this doesn't work, I'm at home and don't have SQL Server or anything else installed to test it (I'm one of those programmers who doesn't code at home :-p)
SELECT
category,
table1count = COUNT(CASE tableid WHEN 1 THEN 1 END),
table2count = COUNT(CASE tableid WHEN 2 THEN 1 END)
FROM (
SELECT 1, category
FROM Table1
UNION ALL
SELECT 2, category
FROM Table2
) x (tableid, category)
GROUP BY category
Select Category,(Select count(id) from t1 where t1.Category=t3.Category),(Select count(id) from t2 where t2.Category=t3.Category) FROM t3
t3 contains
category1 category2 category3
精彩评论