count function and self join [message #19793] |
Wed, 10 April 2002 04:43 |
Blanc Jean-Luc
Messages: 1 Registered: April 2002
|
Junior Member |
|
|
here is my table:
table_A
-------
id_table_a
date_begin
date_end
product
some artcles:
1.................2.................3...................4...................5..........(id_table_a)
10.............17................18.................25.................12..........(date_begin)
15.............22................22.................33.................14..........(date_end)
.......ABA332......1BA332.........AHD25........ABA333..........(product)
Q1:
---
display for each product, the number of those products whose date_begin is between 11 et 19, order by product.
My request is not working:
SELECT count(a.product)
FROM table_A a
WHERE a.DATE_BEGIN(+) between 11 and 19
GROUP BY a.PRODUCT
order by a.PRODUCT
I need some thing like:
------------------------------
2 (for ABA332)
1 (for ABA333)
0 (for AHD25)
------------------------------
when the intervall is between 1 and 2, I need:
------------------------------
0 (for ABA332)
0 (for ABA333)
0 (for AHD25)
------------------------------
------------------------------
Q2:
---
same questions, with those response:
------------------------------
3 (for ABA)
0 (for AHD)
------------------------------
Thank you for your help.
|
|
|
Re: count function and self join [message #19857 is a reply to message #19793] |
Sat, 13 April 2002 17:38 |
Su
Messages: 154 Registered: April 2002
|
Senior Member |
|
|
Just remove the (+) in the expression and try it out. It is only one table, so you dont have to give it, I guess. And also you have shown the example data (column values) in rows where they would be shown in columns, it is confusing. If you remove (+) in the expression and still does not work, let me know with clear example, I will try to help you out.
Good luck :)
|
|
|