Sort with GROUP BY, HAVING

[macaco]

Hi

I've got the following table:

id id_items id_fc nombre cantidad unidad precio moneda
1 1 0 Prod1 9612 unidad 152 dolar
21 1 1 prod3 150 NULL NULL NULL
9 1 0 prod3 500 ton 321 peso
20 1 1 Prod1 100 NULL NULL NULL
19 1 NULL NULL NULL NULL NULL NULL

I'd like to obtain registers grouped by 'nombre', with the data from the one (from the rows with same 'nombre') that has id_fc = 0 or NULL. This would be a unique row.
Also, I want a column to show the result of the SUM of 'cantidad' of all items with that 'nombre' minus the 'cantidad' of the selected item (the one with id_fc = 0 or NULL)

So, I tried the following query:
SELECT * , (SUM( cantidad ) - cantidad) AS facturado FROM PRODUCTOS WHERE id_items = '1' GROUP BY nombre HAVING ( id_fc =0 OR id_fc IS NULL )

The issue, is that I'm gettin this as the result:

id id_items id_fc nombre cantidad unidad precio moneda facturado
19 1 NULL NULL NULL NULL NULL NULL NULL
1 1 0 Prod1 9612 unidad 152 dolar 100

prod3 should appear, but it seems that, before taking the HAVING constraint into account, it's selecting the row that has id_fc > 0 and that's why it doesn't show up.

How should I make the query so that the GROUP BY command selects the row with lower id_fc (zero) or NULL?

Hope I made myself clear.
Many Thanks

[snester]

it's been along time since your post, did you ever get it answered?

I would suggest you do not need to use group by here.. try..

SELECT * , (SUM( cantidad ) - cantidad) AS facturado FROM PRODUCTOS WHERE id_items = '1' AND ( id_fc =0 OR id_fc IS NULL ) ORDER BY nombre

Hope this helps (if you still need it).