Calculated column in SELECT SQL Query (MySQL)

Question

I have the following query:

SELECT
a.source,
TotalCount,
ActiveCount
FROM
(
    SELECT COUNT(*) AS TotalCount, a.source
    FROM internship a
    GROUP BY a.source
) AS A
join
(
    SELECT COUNT(*) AS ActiveCount, b.source
    FROM internship b
    WHERE b.int_lastdate > CURDATE() AND b.status LIKE 'Published'
    GROUP BY b.source
) AS B
    ON A.source = B.source

The above query gives me a result like this:

I want to add another column here that will be "ExpiredCount = TotalCount - ActiveCount"

How can I acheive that?

Tim Biegeleisen · Accepted Answer · 2017-07-16 11:27:16Z

2

Gordon's answer is probably what I would want to see as a DBA inheriting someone else's code. But we could actually avoid using a subquery at all and just write the following:

SELECT
    source,
    COUNT(*) AS TotalCount,
    SUM(CASE WHEN int_lastdate > CURDATE() AND status LIKE 'Published'
             THEN 1 ELSE 0 END) AS ActiveCount,
    COUNT(*) - SUM(CASE WHEN int_lastdate > CURDATE() AND status LIKE 'Published'
                        THEN 1 ELSE 0 END) AS ExpiredCount
FROM internship
GROUP BY source

answered Jul 16, 2017 at 11:27

Tim Biegeleisen

526k32 gold badges324 silver badges399 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Gordon Linoff · Accepted Answer · 2017-07-16 11:43:02Z

2

You simply need an expression for this. However, you can simplify your query:

select i.source, i.TotalCount, i.ActiveCount,
       (i.TotalCount - i.ActiveCount) as ExpiredCount
from (select i.source, count(*) as TotalCount, i.source,
             sum(i.int_lastdate > curdate() and i.status like 'Published') as ActiveCount
      from internship i
      group by i.source
     ) i;

Notes:

Table aliases should be abbreviations for table names, so they are easy to read.
You don't need a join for this query, just conditional aggregation.
Overusing backticks is unnecessary and makes the query harder to write.
The answer to your question is a simple expression.

EDIT:

If you want to use this as a view, you cannot have subqueries in the FROM clause (a MySQL limitation):

      select i.source, count(*) as TotalCount, i.source,
             sum(i.int_lastdate > curdate() and i.status like 'Published') as ActiveCount,
             (count(*) - sum(i.int_lastdate > curdate() and i.status like 'Published')) as ExpiredCount
      from internship i
      group by i.source

edited Jul 16, 2017 at 11:43

answered Jul 16, 2017 at 11:17

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

2 Comments

Rohit Over a year ago

Thanks! The query worked but I am not able to save the query as "view".

Gordon Linoff Over a year ago

@Rohit . . . You couldn't save your original query as a view. Nor is that even mentioned in the question.

Collectives™ on Stack Overflow

Calculated column in SELECT SQL Query (MySQL)

2 Answers 2

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related