Hi all , i want to get out of the data, the most popular good/month i
have in my data, i figured out that it would be the one having the
maximum sales, so i m using the following formula to see for every
month, and every product, and how much were the sales this month,
now the solution would be if i can get the max amount and it is
therefore the product i need, but how could that be done if i have the
following?
==========================================
select (to_char(salinv_1.inv_date, 'MM'))as "MM",
(to_char(salinv_1.inv_date, 'YY')) as "YY",
(sales_1.prod_code) as "Code",
sum(salpmt_1.amount) as "Total Purchase"
from sales_1,salinv_1, salpmt_1
where salinv_1.code = sales_1.inv_code and
to_char(salpmt_1.pmt_date,'MM-YY') = to_char(salinv_1.inv_date, 'MM-
YY')
group by (sales_1.prod_code),
(to_char(salinv_1.inv_date, 'MM')),
(to_char(salinv_1.inv_date, 'YY'))
order by (to_char(salinv_1.inv_date,'YY'))
=============================================
MM YY Code Total Purchase
-- -- --------------- --------------
01 05 SP-20.1 345180
01 05 SP-20.2 396957
01 05 SP-20.3 138072
01 05 SP-20.4 138072
01 05 SP-20.5 241626
01 05 SP-20.6 276144
01 05 SP-20.7 276144
02 05 SP-20.1 328445
02 05 SP-20.2 353710
02 05 SP-20.3 151590
02 05 SP-20.4 303180
02 05 SP-20.5 277915
02 05 SP-20.6 378975
02 05 SP-20.7 176855
============================================
while what i need is like so :
MM YY Code Total Purchase
-- -- --------------- --------------
01 05 SP-20.2 396957
02 05 SP-20.2 353710
============================================
is it possible?
and thank you very much

Totti wrote:
Go to this page:
http://www.psoug.org/reference/analytic_functions.html

And look at the following functions:
DENSE_RANK
MAX
RANK
--
Daniel A. Morgan
Oracle Ace Director & Instructor
University of Washington
damorgan@x.washington.edu (replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org

Totti wrote:
Go to this page:
http://www.psoug.org/reference/analytic_functions.html

And look at the following functions:
DENSE_RANK
MAX
RANK
--
Daniel A. Morgan
Oracle Ace Director & Instructor
University of Washington
damorgan@x.washington.edu (replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org

Totti wrote:
Go to this page:
http://www.psoug.org/reference/analytic_functions.html

And look at the following functions:
DENSE_RANK
MAX
RANK
--
Daniel A. Morgan
Oracle Ace Director & Instructor
University of Washington
damorgan@x.washington.edu (replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org

Totti wrote:
Go to this page:
http://www.psoug.org/reference/analytic_functions.html

And look at the following functions:
DENSE_RANK
MAX
RANK
--
Daniel A. Morgan
Oracle Ace Director & Instructor
University of Washington
damorgan@x.washington.edu (replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org