What kind of SQL clause is this? Any way to convert it to SQL?

Question

Hi,

what kind of SQL is this?

SELECT IFNULL(SUM(prenotazione.VALUTAZIONE),0) AS somma, 
COUNT(*) AS numero 
FROM `prenotazione` 
WHERE prenotazione.USER_ID=18793 AND 
prenotazione.PRENOTAZIONE_STATO_ID IN (10,11)

Im using propel as my ORM.

Any way to convert that kind of SQL to Mysql SQL?

regards

Javi

Answer 1

Using Babelfish to give a rough translation from Italian to English results in

SELECT IFNULL(SUM(reservation.APPRAISAL),0) AS sum,  
COUNT(*) AS number  
FROM `reservation`  
WHERE reservation.USER_ID=18793 AND  
reservation.RESERVATION_STATE_ID IN (10,11) 

Share and enjoy.

Answer 2

This query is valid in MySQL. It selects all rows from the prenotazione table where the user_id is 18793 and the prenotazione_stato_id is 10 or 11. The resulting rows are summarized: in the numero column you get the number of rows found, in the somma column you get the sum of the valutazione values. If no rows were selected, SUM() would return NULL. To prevent this, IFNULL([expr1],[expr2]) is applied, which returns [expr1] if it is not null, and [expr2] if it is null. This makes sure you always return a number.

There is no easy way to do this with Propel, since your result cannot be easily mapped to a Propel object. The best thing you can do is use the underlying database layer (PDO) to escape your parameters and handle the resultset, and you don't open an extra database connection or something like that.

Answer 3

When considering portability, Standard SQL is your friend. This query can be very easily transformed into Standard SQL-92:

1. Terminate the statement with a semi-colon.
2. Replace IFNULL with COALESCE.
3. Remove the single quotes from the table name.

With better spacing it could look like this:

SELECT COALESCE(SUM(prenotazione.VALUTAZIONE), 0) AS somma, 
       COUNT(*) AS numero 
  FROM prenotazione
 WHERE prenotazione.USER_ID = 18793 
       AND prenotazione.PRENOTAZIONE_STATO_ID IN (10,11);

That said, for MySQL you probably would need to undo step 3... which leads me to suspect it was MySQL syntax in the first place.