1

I have two select queries and i want to combine result of both column wise.I have use union between two select queries but it give me just one query result

link

I want expected result like this

enter image description here

My SQL QUERY

    SELECT TC.DESCRIPTION, count(TE.CategoryID) AS COUNT 
   FROM tblCategory TC 
    LEFT OUTER JOIN tblEvent TE
    on TE.CategoryID=TC.NO
    AND TE.AssetID IN (
                        SELECT ASSET_NO 
                        FROM tblAsset 
                        WHERE EQUIPMENT_ID=1
                        )
  GROUP BY  TE.CategoryID,TC.DESCRIPTION 

  UNION 
  SELECT TC.DESCRIPTION as desc2, count(TE.CategoryID) AS COUNT2
  FROM tblCategory TC 
  LEFT OUTER JOIN tblEvent TE
    on TE.CategoryID=TC.NO
    AND TE.AssetID IN (
                        SELECT ASSET_NO 
                        FROM tblAsset 
                        WHERE EQUIPMENT_ID=2
                        )
  GROUP BY  TE.CategoryID,TC.DESCRIPTION 
1

Selecting UNION will append the results below the existing result set. If you want the second query's results as an additional column you will need to write it as a single query and JOIN the additional column.

You can do this by including the 'second' query as a subquery and joining the results together. Without changing your existing query structure:

SELECT      count1.DESCRIPTION,
            count1.[COUNT],
            count2.COUNT2
FROM
(
    SELECT TC.DESCRIPTION,
                count(TE.CategoryID) AS COUNT
    FROM tblCategory TC 
     LEFT OUTER JOIN tblEvent TE
            on TE.CategoryID=TC.NO
            AND TE.AssetID IN (
                            SELECT ASSET_NO 
                            FROM tblAsset 
                            WHERE EQUIPMENT_ID=1
                                )
    GROUP BY  TE.CategoryID,TC.DESCRIPTION 
) count1
LEFT OUTER JOIN
(
    SELECT TC.DESCRIPTION as desc2, count(TE.CategoryID) AS COUNT2
    FROM tblCategory TC 
    LEFT OUTER JOIN tblEvent TE
        on TE.CategoryID=TC.NO
        AND TE.AssetID IN (
                        SELECT ASSET_NO 
                        FROM tblAsset 
                        WHERE EQUIPMENT_ID=2
                        )
    GROUP BY  TE.CategoryID,TC.DESCRIPTION 
) count2 ON count2.desc2 = count1.DESCRIPTION

Of course, this assumes your value for TC.DESCRIPTION is unique, otherwise you will potentially generate duplicate rows. If you have a primary key to join in instead this is preferable, however the underlying table structure was not given in the question.

|improve this answer|||||
  • its alos throwing error Msg 8120, Level 16, State 1, Line 16 Column 'tblCategory.DESCRIPTION' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause. – Hameed Khan Jun 28 '19 at 6:01
  • Apologies, I had missed moving the group by for the initial query into its own subquery, that should be correctly structured now. – Libertheme Jun 28 '19 at 6:05

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.