MySQL: Include COUNT of SELECT Query Results as a Column (Without Grouping)

Question

I have a simple report sending framework that basically does the following things: It performs a SELECT query, it makes some text-formatted tables based on the results, it sends an e-mail, and it performs an UPDATE query.

This system is a generalization of an older one, in which all of the operations were hard coded. However, in pushing all of the logic of what I'd like to do into the SELECT query, I've run across a problem.

Before, I could get most of the information for my text tables by saying:

SELECT Name, Address FROM Databas.Tabl WHERE Status='URGENT';

Then, when I needed an extra number for the e-mail, also do:

SELECT COUNT(*) FROM Databas.Tabl WHERE Status='URGENT' AND TimeLogged='Noon';

Now, I no longer have the luxury of multiple SELECT queries. What I'd like to do is something like:

SELECT Tabl.Name, Tabl.Address, COUNT(Results.UID) AS Totals
FROM Databas.Tabl
LEFT JOIN Databas.Tabl Results
    ON Tabl.UID = Results.UID
    AND Results.TimeLogged='Noon'
WHERE Status='URGENT';

This, at least in my head, says to get a total count of all the rows that were SELECTed and also have some conditional.

In reality, though, this gives me the Mixing of GROUP columns with no GROUP columns illegal if no GROUP BY error. The problem is, I don't want to GROUP BY. I want this COUNT to redundantly repeat the number of results that SELECT found whose TimeLogged='Noon'. Or I want to remove the AND clause and include, as a column in the result of the SELECT statement, the number of results that that SELECT statement found.

GROUP BY is not the answer, because that causes it to get the COUNT of only the rows who have the same value in some column. And COUNT might not even be the way to go about this, although it's what comes to mind. FOUND_ROWS() won't do the trick, since it needs to be part of a secondary query, and I only get one (plus there's no LIMIT involved), and ROW_COUNT() doesn't seem to work since it's a SELECT statement.

I may be approaching it from the wrong angle entirely. But what I want to do is get COUNT-type information about the results of a SELECT query, as well as all the other information that the SELECT query returned, in one single query.

=== Here's what I've got so far ===

SELECT Tabl.Name, Tabl.Address, Results.Totals
FROM Databas.Tabl
LEFT JOIN (SELECT COUNT(*) AS Totals, 0 AS Bonus
           FROM Databas.Tabl
           WHERE TimeLogged='Noon'
           GROUP BY NULL) Results
     ON 0 = Results.Bonus
WHERE Status='URGENT';

This does use sub-SELECTs, which I was initially hoping to avoid, but now realize that hope may have been foolish. Plus it seems like the COUNTing SELECT sub-queries will be less costly than the main query since the COUNT conditionals are all on one table, but the real SELECT I'm working with has to join on multiple different tables for derived information.

The key realizations are that I can GROUP BY NULL, which will return a single result so that COUNT(*) will actually catch everything, and that I can force a correlation to this column by just faking a Bonus column with 0 on both tables.

It looks like this is the solution I will be using, but I can't actually accept it as an answer until tomorrow. Thanks for all the help.

Answer 1

Does this do what you need?

SELECT   Tabl.Name                   ,
         Tabl.Address                ,
         COUNT(Results.UID) AS GrandTotal,
         COUNT(CASE WHEN Results.TimeLogged='Noon' THEN 1 END) AS NoonTotal
FROM     Databas.Tabl
         LEFT JOIN Databas.Tabl Results
         ON       Tabl.UID = Results.UID
WHERE    Status            ='URGENT'
GROUP BY Tabl.Name,
         Tabl.Address
WITH ROLLUP;

Answer 2

The API you're using to access the database should be able to report to you how many rows were returned - say, if you're running perl, you could do something like this:

my $sth  = $dbh->prepare("SELECT Name, Address FROM Databas.Tabl WHERE Status='URGENT'");
my $rv   = $sth->execute();
my $rows = $sth->rows;

Answer 3

You can always put a scalar subquery into the select-list:

SELECT Name, Address, 
  (SELECT COUNT(*) FROM Databas.Tabl 
   WHERE Status='URGENT' AND TimeLogged='Noon') AS C
FROM Databas.Tabl WHERE Status='URGENT';

But this seems fairly idiotic. Why do you need to do this in one single query? Do you write all your programs in a single line of code?

Answer 4

You could probably do a union instead. You'd have to add a column to the original query and select 0 in it, then UNION that with your second query, which returns a single column. To do that, the second query must also select empty fields to match the first.

SELECT Cnt = 0, Name, Address FROM Databas.Tabl WHERE Status='URGENT'
UNION ALL
SELECT COUNT(*) as Cnt, Name='', Address='' FROM Databas.Tabl WHERE Status='URGENT' AND TimeLogged='Noon';

It's a bit of a hack, but what you're trying to do isn't ideal...

Answer 5

Grouping by Tabl.id i dont believe would mess up the results. Give it a try and see if thats what you want.

Answer 6

SELECT Tabl.Name, Tabl.Address, Results.Totals
FROM Databas.Tabl
LEFT JOIN (SELECT COUNT(*) AS Totals, 0 AS Bonus
           FROM Databas.Tabl
           WHERE TimeLogged='Noon'
           GROUP BY NULL) Results
     ON 0 = Results.Bonus
WHERE Status='URGENT';

I figured this out thanks to ideas generated by multiple answers, although it's not actually the direct result of any one. Why this does what I need has been explained in the edit of the original post, but I wanted to be able to resolve the question with the proper answer in case anyone else wants to perform this silly kind of operation. Thanks to all who helped.