Mysql Select Syntax

[TOrpheus]

Hi All,

I've got a table with 2 fields (cost & flag) where 'cost' is a variable number, and 'flag' is either 1 or 0.

Is it possible to run ONE query that would return the following values:

A) Total number of records
😎 Total Average Cost
C) Average Cost if flag == 1

I can do this in TWO queries, but I thought it would be more optimized and quicker if I could run this in ONE.

I'm thinking there should be some sort of IF CONDITION in the SELECT clause, but I don't know how to write that if possible.

Thanks!

[tomhath]

What you are looking for is the CASE statement. You might have to calculate the average on your own, I don't know of a better way to eliminate some of the rows:

select count(*) as TotalCnt,
          avg(cost) as AvgCost,
          ( sum(case when flag = 1 then cost else 0 end) /
            sum(case when flag = 1 then 1 else 0 end) ) as AvgFlag1
from theTable