Michael Plautz Michael Plautz - 1 month ago 4
MySQL Question

SQL: Less invasive way to find difference between two distinct columns

I am specifically targeting SQL Server, but this information would be helpful if it exists for MySQL also. I have found how to create a column that calculates the difference between columns of two consecutive rows, but what I am interested in is how do I calculate the difference between one column in two distinctly identifiable rows?

I know of one way to do it, but it requires deep query nesting and repeating the same base queries. Suppose I have a large query joining multiple tables that ultimately boils down to this:

SELECT
projectStatus AS [Project Status],
COUNT(projectStatus) AS [# of Projects]
FROM
projectList
GROUP BY projectStatus


And let's say it gives the following result:

Project Status | # of Projects
------------------------------
Delayed | 167
Delayed Known | 83
On Time | 92
Ahead | 86


What I would like to do is append a row that calculates the difference between the # of Projects value of rows Delayed and Delayed Known, then omits the Delayed row, like so:

Project Status | # of Projects
------------------------------
Delayed | 167
Delayed Known | 83
On Time | 92
Ahead | 86
Delayed Unknown| 84


Based on the first query, the way I have figured out how to do this looks like:

SELECT
projectStatus AS [Project Status],
COUNT(projectStatus) AS [# of Projects]
FROM
projectList
GROUP BY projectStatus
UNION
SELECT
'Delayed Unknown' AS [Project Status],
SUM([Sum Val]) AS [# of Projects]
FROM (
SELECT
[# of Projects] *
CASE
WHEN [Project Status] = 'Delayed' THEN 1
WHEN [Project Status] = 'Delayed Known'] THEN -1
ELSE 0
END AS [Sum Val]
FROM (
SELECT
projectStatus AS [Project Status],
COUNT(projectStatus) AS [# of Projects]
FROM
projectList
WHERE
projectStatus IN ('Delayed', 'Delayed Known')
GROUP BY projectStatus
) AS queryC
) AS queryB


Keep in mind that the inner query that this is based on is simplified for this post, but it actually is a larger query that is composed of its own
UNION
. Therefore, this gets ugly very quickly and approaches hard-to-maintain status.

The target for this query is to act as a dataset for SQL Server Reporting Services, so my constraint is to do this all in one query (unless it is possible to use temp tables within SSRS datasets). So, in one query, is there a less invasive way to do this calculation?

Answer

Please try this, may be you are looking for something like the below.

;with cte as(
    select Projectstatus, count(*) as [# of Projects]
    from @Table 
    where projectStatus IN ('Delayed', 'Delayed Known')
    group by ProjectStatus
), cte2 as(
    select 
        (Select [# of Projects] From cte Where Projectstatus = 'Delayed')
        -(Select [# of Projects] From cte Where Projectstatus = 'Delayed Known')  as DelayedUnKownProjects
)
    select Projectstatus, [# of Projects] 
    From cte
    UNION ALL 
    SELECT 'Delayed UnKnown' as Projectstatus, DelayedUnKownProjects as [# of Projects]
    From cte2 
Comments