Product date salesp1 20/Jan/1999 100p1 20/Feb/1999 200p1 20/apl/1999 100p1 20/DEC/1999 50p1 20/Jan/2000 100p1 20/mar/2000 500now i want query like product ,sum of actual year ,sum of business yearp1 450 750here actual year means 1 Jan 1999 to 31 st Jan 1999business year means 1 st APR 1999 to 31 st mar 200pls give me answer as early as possible..

Sid

Apr 3rd, 2006

guess you can try this
(select product,sales S_A_Y from info where date >= 1 st Jan 1999 & date =<
31 st Jan 1999 groupby product) sal1 ,
(select product,sales S_B_Y from info where date >= 1 st Apr 1999 & date =<
31 st Mar 2000 groupby product) sal2
where sal1.product=sal2.product ;

reddeppa

Apr 4th, 2006

hai.....iam not understand what u says..can give me deatil....what is sal1.product=sal2.product..actually waht sal1 and sal2 are column alises,,,?right?
then why u put these are in conditions??/
plz give me clearly.

pat.mclean
Profile Answers by pat.mclean Questions by pat.mclean

Apr 20th, 2006

Hi thr!
for reusability try breaking the dates into days & months & year. use them in the given statement(instead of hard-coding dates).
(select product,sales S_A_Y from info where date >= 1 st Jan 1999 & date =<
31 st Jan 1999 groupby product) sal1 ,
(select product,sales S_B_Y from info where date >= 1 st Apr 1999 & date =<
31 st Mar 2000 groupby product) sal2
where sal1.product=sal2.product ;
Can you please specify what exactly u want as "sum of actual year"? is it total number of years ?
Cheers,
Pat

Yathish Uppala

Oct 10th, 2007

I am providing query(oracle) below.
Plz go through it.

SELECT SAL1.PRODUCT,SAL1.ACTUALSAL,SAL2.BUSINESSSAL FROM (SELECT PRODUCT,SUM(SALES) AS ACTUALSAL FROM PRODUCTS
                                                          WHERE PDATE BETWEEN '1-JAN-1999' AND '31-DEC-1999'
                                                          GROUP BY PRODUCT) SAL1,
                                                         (SELECT PRODUCT,SUM(SALES) AS BUSINESSSAL FROM PRODUCTS
                                                          WHERE PDATE BETWEEN '1-APR-1999' AND '31-MAR-2000'
                                                          GROUP BY PRODUCT) SAL2
WHERE SAL1.PRODUCT = SAL2.PRODUCT

vuongti
Profile Answers by vuongti

Dec 2nd, 2009

Here is the sql script

select
product
,Act_year = (select sum(amt) from tblProd where Date between '01/01/1999' and '12/31/1999')
,Bus_year = (select sum(amt) from tblProd where Date between '04/01/1999' and '03/31/2000')
from tblProd
group by product