to frame cost effective query

Could you please help me out in building a Query. I Have a table as below

Id            Info_Id                 Type
1              2                              2
2              6                              2
3              5                              3
4              8                              3
5              2                              3
6              2                              2
7              5                              2
8              8                              2
9              5                              2
10             8                              2
11             8                              2
12             5                              3
13             6                              3
14             8                              3

a query need to be framed so as to group by "Info_Id".

I开发者_如何学JAVA need output as below for eg:

Info_Id CountOfRec       Type2    Type3
2              3         2              1
5              4         2              2
6              2         1              1
8              5         3              2

I Tried as below but I m not able to get the efficient output

select  Info_Id, count(Id)as CountOfRec,
(select count(Id)from tbl_TypeInfo where Info_Id = 5 AND Type = 2) as Type2,
(select count(Id)from tbl_ TypeInfo where Info_Id = 5 AND Type = 3) as Type3
 from tbl_TypeInfo
where Info_Id = 5
group by Info_Id

output was this ,

Info_Id CountOfRec       Type2    Type3
5              4                              2              2

(I have to loop for each “Info_id” to get desired OP, there is thousand of records and its time consuming)

I wanted to get the highlighted output from the table. The query I have framed is not efficient and there would be good solution for this can you help me out.

You can use the CASE expression to only count the rows for a specific type:

SELECT Info_Id,
    COUNT(*) AS CountOfRec,
    COUNT(CASE WHEN Type = 2 THEN 1 ELSE NULL END) AS Type2
    COUNT(CASE WHEN Type = 3 THEN 1 ELSE NULL END) AS Type3
FROM tbl_TypeInfo
GROUP BY Info_Id

Add a WHERE Info_Id = 5 to retrieve results for a specific ID only.

Update: as per comments, if you do not store a table of ID's, You need to change your IN(..) list to a virtual "table":

SELECT vt.id,
    COUNT(*) AS CountOfRec,
    COUNT(CASE WHEN Type = 2 THEN 1 ELSE NULL END) AS Type2,
    COUNT(CASE WHEN Type = 3 THEN 1 ELSE NULL END) AS Type3
FROM (
    SELECT 1 id
    UNION SELECT 2
    UNION SELECT 3
    UNION SELECT 5
    UNION SELECT 8 
  ) AS vt LEFT JOIN tbl_TypeInfo ON vt.id = tbl_TypeInfo.Info_Id
GROUP BY vt.id

You can use SQL Server's PIVOT operator

SELECT  Info_ID
        , CountOfRec = [2] + [3]
        , Type2 = [2]
        , Type3 = [3]
FROM    (        
          SELECT  *
          FROM    (          
                    SELECT  *
                    FROM    tbl_TypeInfo
                   ) s 
          PIVOT   (COUNT(Id) FOR Type IN ([2], [3])) pvt 
        ) q

Test

;WITH tbl_TypeInfo AS (
  SELECT [Id] = 1, [Info_Id] = 2, [Type] = 2
  UNION ALL SELECT 2, 6, 2
  UNION ALL SELECT 3, 5, 3
  UNION ALL SELECT 4, 8, 3
  UNION ALL SELECT 5, 2, 3
  UNION ALL SELECT 6, 2, 2
  UNION ALL SELECT 7, 5, 2
  UNION ALL SELECT 8, 8, 2
  UNION ALL SELECT 9, 5, 2
  UNION ALL SELECT 1, 8, 2
  UNION ALL SELECT 1, 8, 2
  UNION ALL SELECT 1, 5, 3
  UNION ALL SELECT 1, 6, 3
  UNION ALL SELECT 1, 8, 3
)
SELECT  Info_ID
        , CountOfRec = [2] + [3]
        , Type2 = [2]
        , Type3 = [3]
FROM    (        
          SELECT  *
          FROM    (          
                    SELECT  *
                    FROM    tbl_TypeInfo
                   ) s 
          PIVOT   (COUNT(Id) FOR Type IN ([2], [3])) pvt 
        ) q

This is a bit crazy, but if the types are always, always 2 and 3, this could be threated as an equation, where count(type2)+count(type3)=count(*) and 2*count(type2)+3*count(type3)=sum(*) so you could to something like

SELECT 3*c-s as Type2Count, s-2*c as Type3Count
FROM (SELECT COUNT(*) as C, SUM(Type) as S 
      FROM tbl_TypeInfo
      WHERE Info_Id = 5) SourceTable

This will be lightning fast, however, this is extemely breakable!!!! If ever, the types are changed, or a type is added, this will not work.