any way to get MAX?
From: Totti <saliba.toufic.george_at_gmail.com>
Date: Sun, 6 Jan 2008 11:02:12 -0800 (PST)
Message-ID: <eb4444a5-56ba-4458-93c7-87fd3416703e@d21g2000prf.googlegroups.com>
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, 'MMYY')
(to_char(salinv_1.inv_date, 'MM')),
(to_char(salinv_1.inv_date, 'YY'))
order by (to_char(salinv_1.inv_date,'YY'))
and thank you very much Received on Sun Jan 06 2008 - 13:02:12 CST
Date: Sun, 6 Jan 2008 11:02:12 -0800 (PST)
Message-ID: <eb4444a5-56ba-4458-93c7-87fd3416703e@d21g2000prf.googlegroups.com>
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, 'MMYY')
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 Received on Sun Jan 06 2008 - 13:02:12 CST