返回平均值的最有效方式+ 3列的计数（每个都有子句）

Question

I have a semi-complex query I need to carry out which pulls data from three columns (idea, execution and market). I have it working for 1 column independently, and now I need to adapt it to work for all 3 columns. Is there a more efficient way of handling this situation or should I just run 3 queries back-to-back?

Here's the single query that works:

function returnRatings($id) {
    $this->db->select('COUNT(*) AS ideacount');
    $this->db->select_avg('idea','ideaavg');
    $this->db->where('startupid',$id);
    $this->db->where('idea >', '0');

    $query = $this->db->get('ratings');

    return $query->row();
}

I need to also pull the same data for the columns 'market' and 'execution' (average of the rows + total number of rows where column > 0). The hard part is that for each data pull, I need the a clause that says

WHERE column > 0

To account for a case where they chose to vote in some categories, but not in others. Anyone have any suggestions?

Answer 1

If I understand correctly, it seems there's not correlation for the three columns likelihood of having column > 0. In that case, I don't think there is much speed-up to be gained by executing this as a single query rather than as 3.

If I were you I would write it as 3 queries and see if it's an acceptably fast operation. If so, that may be the best and easiest-to-read option.