locked
Combine 2 queries RRS feed

  • Question

  • I have very limited understanding of SQL queries, and need to add more columns to a query that I already have, but not sure how it's done. So this query is returning the count of each OS version in the collection CM000001. How can I add more columns to this for collections CM000002, CM000003, and so forth?

    SELECT rsys.Name0 AS 'Computer Name', Caption0 as 'Operating System'
    FROM v_R_System rsys
    JOIN v_GS_OPERATING_SYSTEM os ON rsys.ResourceID = os.ResourceID
    JOIN v_FullCollectionMembership fcm on os.ResourceID = fcm.ResourceID
    WHERE fcm.CollectionID = 'CM000001' or fcm.CollectionID = 'CM000001'
    ORDER BY rsys.Name0

    +++++++++++++++++++++++++++++++++++++++++++++++

    SELECT rsys.Name0 AS 'Computer Name', Caption0 as 'Operating System'
    FROM v_R_System rsys
    JOIN v_GS_OPERATING_SYSTEM os ON rsys.ResourceID = os.ResourceID
    JOIN v_FullCollectionMembership fcm on os.ResourceID = fcm.ResourceID
    WHERE fcm.CollectionID = 'CM000002' or fcm.CollectionID = 'CM000002'
    ORDER BY rsys.Name0

    Sunday, September 1, 2019 4:03 PM

All replies

  • Deleted
    Monday, September 2, 2019 1:48 AM
  • Did you meant to add more data rows, to include more collectionID case, to the query result? If so, in the where clause you need to change the condition to include more rows. add more "or" condition, or like Jose's code using "IN" for a collection. 


    MSDN Community Support Please remember to click Mark as Answer; the responses that resolved your issue, and to click Unmark as Answer if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Monday, September 2, 2019 2:18 AM
  • Hi Raydar12

    Add you tables and example data. After this we will try to help you.

    Best Regards,

    Natig


    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. 

    Monday, September 2, 2019 5:36 AM
  • How can I add more columns to this for collections CM000002, CM000003, and so forth?

    Try

    -- code #1 v2
    SELECT fcm.CollectionID, rsys.Name0 AS [Computer Name], Caption0 as [Operating System]
      FROM v_R_System rsys
           JOIN v_GS_OPERATING_SYSTEM os ON rsys.ResourceID = os.ResourceID
           JOIN v_FullCollectionMembership fcm on os.ResourceID = fcm.ResourceID
      WHERE fcm.CollectionID in ('CM000001', 'CM000002', 'CM000003')
      ORDER BY fcm.CollectionID, rsys.Name0;
     
    (...) this query is returning the count of each OS version in the collection

    Try

    -- code #2
    SELECT fcm.CollectionID, Caption0 as [Operating System],
           count(*) as Qty
      FROM v_R_System rsys
           JOIN v_GS_OPERATING_SYSTEM os ON rsys.ResourceID = os.ResourceID
           JOIN v_FullCollectionMembership fcm on os.ResourceID = fcm.ResourceID
      WHERE fcm.CollectionID in ('CM000001', 'CM000002', 'CM000003')
      GROUP BY fcm.CollectionID, Caption0
      ORDER BY fcm.CollectionID, Caption0;

    If this answer was helpful, remember to mark it


    José Diz     Belo Horizonte, MG - Brasil     [T-SQL performance tuning: Porto SQL]


    Este conteúdo é fornecido sem garantias de qualquer tipo, seja expressa ou implícita.

    Thanks Jose. The 2nd collection results should show as a new column, not to be added to the bottom of the results of the 1st collection. Below is what I'm getting with your query and I actually need to get something like this:


    Monday, September 2, 2019 6:15 AM
  • Did you meant to add more data rows, to include more collectionID case, to the query result? If so, in the where clause you need to change the condition to include more rows. add more "or" condition, or like Jose's code using "IN" for a collection. 


    MSDN Community Support Please remember to click Mark as Answer; the responses that resolved your issue, and to click Unmark as Answer if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Thanks for your reply. Above, I tried to explained it with an image this time. Hopefully this one helps.
    Monday, September 2, 2019 6:16 AM
  • Hi,

    I notice that the ColloctionID in you pic is different than that in the query? Could you offer some sample data. 

    Do you have three ColloctionID that correspond to qty1, qty2, qty3 ? If so, please try:

    ;With ori as(
    SELECT fcm.CollectionID, Caption0 as [Operating System],
           count(*) as Qty
      FROM v_R_System rsys
           JOIN v_GS_OPERATING_SYSTEM os ON rsys.ResourceID = os.ResourceID
           JOIN v_FullCollectionMembership fcm on os.ResourceID = fcm.ResourceID
      WHERE fcm.CollectionID in ('CM000001', 'CM000002', 'CM000003')
      GROUP BY fcm.CollectionID, Caption0
      ORDER BY fcm.CollectionID, Caption0)
    cte as (
    SELECT [Operating System],Qty,dense_rank()over(order by CollectionID ) dr
    from ori)
    SELECT [Operating System],[1] Qty1, [2] Qty2 ,[3] Qty3
    FROM cte
    PIVOT (
        MAX(Qty)
        FOR dr IN ([1], [2],[3])
    ) AS pvt
    


     

    MSDN Community Support Please remember to click Mark as Answer; the responses that resolved your issue, and to click Unmark as Answer if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.



    Monday, September 2, 2019 7:49 AM
  • Deleted
    Monday, September 2, 2019 12:20 PM