[SOLVED] Is this a valid query?

[dshevnock]

I don't have much experience writing mysql statements with IF statements. I did however come up with this one and wanted to know if this query was valid/make sense/logical/etc.

 

SELECT

  sum( IF(pos.created_by= '8',1,0))  AS userOpenedTotalCount,

  sum( IF(pos.updated_by = '8' AND pos_details.closed = 'Y',1,0)) as userClosedTotalCount,

  sum( IF( pos.created_date BETWEEN '2007-07-02 00:00:00' AND '2007-07-02 23:59:59' AND pos.created_by = '8', 1, 0 ) ) AS userOpenedTodayCount,

  sum( IF( pos.last_updated_date BETWEEN '2007-07-02 00:00:00' AND '2007-07-02 23:59:59' AND pos.updated_by = '8' AND pos_details.closed = 'Y', 1, 0 ) ) AS userClosedTodayCount

FROM pos JOIN pos_details ON pos.id = pos_details.id

 

Or is there a better way to write this?

[Wildbug]

SELECT 
   SUM( pos.created_by = '8')  AS userOpenedTotalCount, 
   SUM( pos.updated_by = '8' AND pos_details.closed = 'Y') as userClosedTotalCount, 
   SUM( pos.created_date BETWEEN '2007-07-02 00:00:00' AND '2007-07-02 23:59:59' AND pos.created_by = '8') AS userOpenedTodayCount, 
   SUM( pos.last_updated_date BETWEEN '2007-07-02 00:00:00' AND '2007-07-02 23:59:59' AND pos.updated_by = '8' AND pos_details.closed = 'Y' ) AS userClosedTodayCount 
FROM pos JOIN pos_details ON pos.id = pos_details.id

 

It makes sense, and it's a good way to count.  It can be made shorter, though, by using the property of a boolean comparison in MySQL that true=1 and false=0 already.  In other words, if you're just doing an IF(condition,1,0), you don't need the IF() since the comparison will produce either 1 if true or 0 if false anyway.

[dshevnock]

Thanks Wildbug! That worked superbly!