I am trying to find a better way to do this. I have a subquery getting a "total" in the select clause but I want that returned value used in the main queries where like

SELECT , (SELECT COUNT() FROM tableA) AS total
FROM tableB
WHERE total > 5

This of course gives me an error says total doesn't exist. I can get this to work by doing this

SELECT , (SELECT COUNT() FROM tableA) AS total
FROM tableB
WHERE (SELECT COUNT(*) FROM tableA) > 5

You are probably asking why not just remove it from the select, well this query is used to list data from the database and "total" is a value I want to get, only if someone uses a filter and tells me that they need total to be a certain value do I need to limit the results on total so the subquery in the select is always there. The subquery results are not always used in the condition (the "where" conditions are automatically added or removed based on user filters), so is there a way to do this without having to run two subquires when they want to limit the results on the subquery total?

Thanks,
Danny

    select *
  from (select tableB.*, 
               (select count(*)
                  from tableA) as total 
          from tableB) dt
 where total > 5

      But do note that you are not in any way relating anything in table a to anything in table b. Thus you are selecting all columns for all rows in table b, and to each of those rows you also add a column containing the number of rows of A. In other words, if table a has 6 rows or more, you select all rows form table b. If A has 5 rows or less, you select no rows from table b.

      Also note that you could have changed your first query to work by changing WHERE to HAVING. Having does pretty much the same thing as where, except that it does not limit the number of rows retrieved. It instead discards rows after they are retrieved.

        Also note that you could have changed your first query to work by changing WHERE to HAVING.

        That will not work in all DBMS and it is also not compliant with the SQL standard.

          As far as I've understood the standard it is conformant, since it says that each column reference in the having clause should be a reference to a grouping column or an outer reference. Well, the last one I've ever looked at was SQL 2003.

            I don't see that the outer reference makes any difference to the whether the query is standard compliant. That is to allow for queries like

            select *
  from xxx
 where c2 in 
      (select max(c2)
         from yyy
        group
           by c3
       having xxx.c1 = 1)

            An outer reference is a column referenc to a column in a table that is outside the actual query.

              I thought that'd apply to the alias which is referencing count(*) in the subquery as well. Perhaps I've missunderstood things?

                Write a Reply...