MySQL: include COUNT from SELECT query results as a column (no grouping)

Question

I have a simple reporting structure that basically does the following: It executes a SELECT query, it creates tables with text formatting based on the results, sends an email and executes an UPDATE query.

This system is a generalization of the older version, in which all operations were hard-coded. However, pushing all the logic of what I would like to do in the SELECT query, I ran into a problem.

Before that, 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 email number, also do:

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

Now I no longer have the luxury of several SELECT queries. What I would 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';

, , , , , , .

, "1140 - GROUP" - GROUP, GROUP BY ". , GROUP BY. , COUNT , SELECT, TimeLogged =" Noon". AND SELECT , SELECT.

GROUP BY , COUNT , . COUNT, , , , , . FOUND_ROWS() , , ( LIMIT), ROW_COUNT(), , , SELECT.

. COUNT SELECT, , SELECT, .

=== ===

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';

-SELECT, , , , , . , , COUNTING SELECT , , COUNT , SELECT, , .

, GROUP BY NULL, , COUNT (*) , , Bonus 0 .

, , , . .

Answer 1

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';

, , . , , , , - . , .

Answer 2

. 0, UNION, , . .

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';

, , , ...

Answer 3

, ?

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 4

The API that you use to access the database should be able to tell you how many rows were returned - let's say if you use perl, you can 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 5

Grouping by Tabl.id I do not believe this will ruin the results. Try it and see what you want.