开发者

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

0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜