469,128 Members | 1,655 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,128 developers. It's quick & easy.

Sort with GROUP BY, HAVING

9
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
Jun 28 '08 #1
1 3279
snester
12
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).
Oct 6 '08 #2

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

7 posts views Thread by Mark | last post: by
2 posts views Thread by Ken R. | last post: by
4 posts views Thread by Brett | last post: by
21 posts views Thread by yeti349 | last post: by
1 post views Thread by Martin.Molch | last post: by
1 post views Thread by CARIGAR | last post: by
reply views Thread by zhoujie | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.