[SOLVED] Simplify select with count

Issue

I need to get some values from a database and count all rows.

I wrote this code:

SELECT author, alias, (select COUNT(*) from registry WHERE status='OK' AND type='H1') AS count
FROM registry
WHERE status='OK' AND type='H1'

It works, but, how can I simplify this code? Both WERE condition thereof.

Solution

If the query is returning the resultset you need, with the “total” count of rows (independent of author and alias), with the same exact value for “count” repeated on each row, we could rewrite the query like this:

SELECT t.author
     , t.alias
     , s.count
  FROM registry t
 CROSS
  JOIN ( SELECT COUNT(*) AS `count`
           FROM registry c
          WHERE c.status='OK'
            AND c.type='H1'
       ) s
 WHERE t.status='OK'
   AND t.type='H1'

I don’t know if that’s any simpler, but to someone reading the statement, I think it makes it more clear what resultset is being returned.

(I also tend to favor avoiding any subquery in the SELECT list, unless there is a specific reason to add one.)

The resultset from this query is a bit odd. But absent any example data, expected output or any specification other than the original query, we’re just guessing. The query in my answer replicates the results from the original query, in a way that’s more clear.

Answered By – spencer7593

Answer Checked By – Mary Flores (BugsFixing Volunteer)