subtract one totals query from another

I have two queries -
I need to be able to subtract query 1
query 1:

  SELECT CLX_DESC as 'Application', COUNT(A.CLE_EVID)as 'Count',
      2 'OrderBy',AVG(DATEDIFF(SS, A.CLE_TIME, B.CLE_TIME)) 'TotalLength', UTILITY_NAME 'Utility'
      FROM CL_EVENTS_IDX WITH (NOLOCK)
      INNER JOIN CL_EVENTS A WITH (NOLOCK) ON CLE_IDX_KEY = CLE_ID
      INNER JOIN UTILITY_INFO WITH (NOLOCK) ON CLE_IDX_UCODE = UTILITY_KEY
      INNER JOIN CL_EVENTS B WITH (NOLOCK) ON A.CLE_ID = B.CLE_ID AND A.CLE_CALLID = B.CLE_CALLID
      INNER JOIN CL_APPID_XREF WITH (NOLOCK) on A.CLE_EVID = CLX_APPID
      WHERE A.CLE_TYPE = 508 AND B.CLE_TYPE = 536 and a.CLE_EVID = b.CLE_EVID
      AND (A.CLE_EVID = 12 OR A.CLE_EVID = 13)
      --AND A.CLE_TIME between '2012-02-05 07:30' and '2012-02-05 07:45'
      GROUP BY UTILITY_NAME, CLX_DESC
      ORDER BY OrderBy,Application
 
from query 2
query 2
SELECT 'All Calls' AS 'Application', COUNT(CLE_IDX_OFFHOOK) AS 'Count',
      isnull(avg(DATEDIFF(ss, CLE_IDX_OFFHOOK, CLE_IDX_ONHOOK)),0) 'TotalLength', 1 'OrderBy', UTILITY_NAME 'Utility'
      FROM CL_EVENTS_IDX WITH (NOLOCK) INNER JOIN UTILITY_INFO WITH (NOLOCK) ON CLE_IDX_UCODE = UTILITY_KEY
      GROUP BY UTILITY_NAME

in the final return - i need to have both the original results of query 2 and the results of (query 2 - query 1)
TagomAsked:
Who is Participating?
 
sachitjainCommented:
Then I guess Application should be the common column between 2 resultsets. Based on this assumption, following could be your query

select x.[Application], x.[Count] - y.[Count] 'CountDiff', x.TotalLength, y.TotalLength, x.[OrderBy], y.[OrderBy], x.[Utility], y.[Utility]
from
      (SELECT 'All Calls' AS 'Application',
                        COUNT(CLE_IDX_OFFHOOK) AS 'Count',
                        isnull(avg(DATEDIFF(ss, CLE_IDX_OFFHOOK, CLE_IDX_ONHOOK)),0) 'TotalLength',
                        1 'OrderBy',
                        UTILITY_NAME 'Utility'
      FROM CL_EVENTS_IDX WITH (NOLOCK) INNER JOIN UTILITY_INFO WITH (NOLOCK) ON CLE_IDX_UCODE = UTILITY_KEY
      GROUP BY UTILITY_NAME) x
      LEFT JOIN
      (SELECT CLX_DESC as 'Application',
                        COUNT(A.CLE_EVID)as 'Count',
                        AVG(DATEDIFF(SS, A.CLE_TIME, B.CLE_TIME)) 'TotalLength',
                        2 'OrderBy',
                        UTILITY_NAME 'Utility'
      FROM CL_EVENTS_IDX WITH (NOLOCK)
              INNER JOIN CL_EVENTS A WITH (NOLOCK) ON CLE_IDX_KEY = CLE_ID
              INNER JOIN UTILITY_INFO WITH (NOLOCK) ON CLE_IDX_UCODE = UTILITY_KEY
              INNER JOIN CL_EVENTS B WITH (NOLOCK) ON A.CLE_ID = B.CLE_ID AND A.CLE_CALLID = B.CLE_CALLID
              INNER JOIN CL_APPID_XREF WITH (NOLOCK) on A.CLE_EVID = CLX_APPID
      WHERE A.CLE_TYPE = 508 AND B.CLE_TYPE = 536 and a.CLE_EVID = b.CLE_EVID
              AND (A.CLE_EVID = 12 OR A.CLE_EVID = 13)
              --AND A.CLE_TIME between '2012-02-05 07:30' and '2012-02-05 07:45'
      GROUP BY UTILITY_NAME, CLX_DESC) y
      ON X.[Application] = Y.[Application]
ORDER BY X.OrderBy, x.[Application]
0
 
sachitjainCommented:
SELECT 'All Calls' AS 'Application',
            COUNT(CLE_IDX_OFFHOOK) AS 'Count',
            isnull(avg(DATEDIFF(ss, CLE_IDX_OFFHOOK, CLE_IDX_ONHOOK)),0) 'TotalLength',
            1 'OrderBy',
            UTILITY_NAME 'Utility'
FROM CL_EVENTS_IDX WITH (NOLOCK) INNER JOIN UTILITY_INFO WITH (NOLOCK) ON CLE_IDX_UCODE = UTILITY_KEY
GROUP BY UTILITY_NAME
Except
SELECT CLX_DESC as 'Application',
            COUNT(A.CLE_EVID)as 'Count',
            AVG(DATEDIFF(SS, A.CLE_TIME, B.CLE_TIME)) 'TotalLength',
            2 'OrderBy',
            UTILITY_NAME 'Utility'
FROM CL_EVENTS_IDX WITH (NOLOCK)
      INNER JOIN CL_EVENTS A WITH (NOLOCK) ON CLE_IDX_KEY = CLE_ID
      INNER JOIN UTILITY_INFO WITH (NOLOCK) ON CLE_IDX_UCODE = UTILITY_KEY
      INNER JOIN CL_EVENTS B WITH (NOLOCK) ON A.CLE_ID = B.CLE_ID AND A.CLE_CALLID = B.CLE_CALLID
      INNER JOIN CL_APPID_XREF WITH (NOLOCK) on A.CLE_EVID = CLX_APPID
WHERE A.CLE_TYPE = 508 AND B.CLE_TYPE = 536 and a.CLE_EVID = b.CLE_EVID
      AND (A.CLE_EVID = 12 OR A.CLE_EVID = 13)
      --AND A.CLE_TIME between '2012-02-05 07:30' and '2012-02-05 07:45'
GROUP BY UTILITY_NAME, CLX_DESC
ORDER BY OrderBy,Application
0
 
TagomAuthor Commented:
This solution DOES NOT change the totals returned. It seems to make no difference what so ever.

any other suggestions
0
Cloud Class® Course: Python 3 Fundamentals

This course will teach participants about installing and configuring Python, syntax, importing, statements, types, strings, booleans, files, lists, tuples, comprehensions, functions, and classes.

 
sachitjainCommented:
I did not understand

You want to subtract your second resultset from first one or subtract column values. If the second one then which column value you want to subtract from which column value?
0
 
TagomAuthor Commented:
I need to subtract columns,
COUNT(CLE_IDX_OFFHOOK) AS 'Count' -  COUNT(A.CLE_EVID)as 'Count'  

The rest of the columns remain the same
0
 
TagomAuthor Commented:
okay, going with this query I have culled out the columns to exactly what I need, (removed the y.app.....)
However the x.count - y.count 'count' returns a null
I have tried a few changes none are working -
select x.[Application], (x.[Count] - y.[Count]) AS 'Count', x.TotalLength, x.[OrderBy], x.[Utility]
from
      (SELECT 'All Calls' AS 'Application',
                        COUNT(CLE_IDX_OFFHOOK) AS 'Count',
                        isnull(avg(DATEDIFF(ss, CLE_IDX_OFFHOOK, CLE_IDX_ONHOOK)),0) 'TotalLength',
                        1 'OrderBy',
                        UTILITY_NAME 'Utility'
      FROM CL_EVENTS_IDX WITH (NOLOCK) INNER JOIN UTILITY_INFO WITH (NOLOCK) ON CLE_IDX_UCODE = UTILITY_KEY
      
      GROUP BY UTILITY_NAME) x
    left JOIN
      (SELECT CLX_DESC as 'Application',
                        COUNT(A.CLE_EVID)as 'Count',
                        AVG(DATEDIFF(SS, A.CLE_TIME, B.CLE_TIME)) 'TotalLength',
                        2 'OrderBy',
                        UTILITY_NAME 'Utility'
      FROM CL_EVENTS_IDX WITH (NOLOCK)
              INNER JOIN CL_EVENTS A WITH (NOLOCK) ON CLE_IDX_KEY = CLE_ID
              INNER JOIN UTILITY_INFO WITH (NOLOCK) ON CLE_IDX_UCODE = UTILITY_KEY
              INNER JOIN CL_EVENTS B WITH (NOLOCK) ON A.CLE_ID = B.CLE_ID AND A.CLE_CALLID = B.CLE_CALLID
              INNER JOIN CL_APPID_XREF WITH (NOLOCK) on A.CLE_EVID = CLX_APPID
      WHERE A.CLE_TYPE = 508 AND B.CLE_TYPE = 536 and a.CLE_EVID = b.CLE_EVID
              AND (A.CLE_EVID = 12 OR A.CLE_EVID = 13)
              --AND A.CLE_TIME between '2012-02-05 07:30' and '2012-02-05 07:45'
      GROUP BY UTILITY_NAME, CLX_DESC) y
      ON X.[Application] = Y.[Application]
      GROUP BY x.[Application],x.TotalLength, x.[OrderBy], x.[Utility],x.Count,y.Count
ORDER BY X.OrderBy, x.[Application]

Open in new window

0
 
sachitjainCommented:
This query would only work if you have applications names as common in 2 resultsets. Is it the case?
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.