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.
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)