CODEWITHSUNDEEP
mysqlsql
Rohit
Rohit

Reputation: 87

Calculated column in SELECT SQL Query (MySQL)

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:

enter image description here

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

How can I acheive that?

Upvotes: 0

Views: 12389

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269623

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

Upvotes: 2

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521053

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

Upvotes: 2

Related Questions