Here is a trick that I always forget despite how handy it is. Often I want to have a count of certain records I am dealing with as columns in a query that returns more than each individual count. Lets put that in English with an example:
Say I have a table people that contains (name, age). I want to get all of the rows out of this table, along with a count of people who fall into certain age groups, lets say >= 18 and
select people.*,
(select count * from people where age >= 18) as adults,
(select count * from people where age from people;
If you look at the query plan for this type of query you’ll see that it goes through the table three separate times! Not good.
You can get the same results using a case statement like so:
select people.*,
count(case when age >= 18 then age end) as adults,
count(case when age from people
group by “name”, age;
This query only goes through the table one time and give you row specific information, meaning it tells you what category, name and age once! (or one record at a time).
—–Post from outside user—–
RECOMMENDATION FROM CHRISTIE:
I recommend never using select * or count(*)… I know that this was just an example, but you should always consider your indexes, records, and tables before doing a select *. Even doing counts, you can always count by the primary key which will always have an index (or at least ALWAYS SHOULD!!! Ha!)
Many people still use “SELECT *”, but I would recommend only selecting the column names that you need “returned”, not that you are working with. For example, many people may include columns in their SELECT statement that they would include in their WHERE clause… this is simply not the case. Your SELECT columns are only necessary if you intend to use them in output or in further functions locally.
Again, I know this was just an example and I even use SELECT * in examples from time to time, but I just wanted to throw in the tip for a real world example, as I have seen time and time again experienced programmers still using SELECT *.
