total and group by with where clause in sub query?

[venkat6134]

Hi all,

i have two tables. Tab1, Tab2.

Tab1 having columns as,

itemno
distcode(A,B,C,D)

Tab2 having columns as:

itemno
total1
total2
total3
creditdate( like 01-mar-11)

Now i want the report for (A,B)distcodes as like this;

month sum(total1) sum(total2) sum(total3)
mar-2011 xxxx xxxx xxxx
apr-2011 xxxx xxxx xxxx


Please reply me soon.
Thanking you.

[guido2004]

What is the problem you're having?

[r937]

creditdate( like 01-mar-11)

this looks like a VARCHAR column

since you didn't mention which database you're using, i'm going to assume it's mysql, and i'm also going to assume that creditdate is an actual DATE column, and not a VARCHAR

SELECT DATE_FORMAT(creditdate,'%b-%Y') AS mthyyyy
     , SUM(total1) AS sum_total1
     , SUM(total2) AS sum_total2
     , SUM(total3) AS sum_total3
  FROM tab1
INNER
  JOIN tab2
    ON tab2.itemno = tab1.itemno
 WHERE tab1.distcode IN ( 'A','B' )
GROUP
    BY mthyyyy

if either of my assumptions is not true, you will ahve to change the first line of the SELECT

[venkat6134]

Thanking you for ur reply,

The database is Oracle 10g. and the creditdate column is in date format only.
Give me the reply that will work under Oracle 10g.

[r937]

look up TO_CHAR in your manual

[venkat6134]

thanks, i got for month with to_char.
Now i want the monthlywise totals. For (A,B) distcode in each total column where the itemno in Tab1 is equal to itemno in Tab2 ?

[r937]

Now i want the monthlywise totals.

see post #3

[venkat6134]

Hi,
When i was tried post#3, Oracle showing error is: Invalid Group by identifier 'mthyyyy'.
I tried by changing the name also. Its not showing the result.

Please give me the correct query that will work in Oracle 10g.

Thanking you...

[r937]

When i was tried post#3, Oracle showing error is: Invalid Group by identifier 'mthyyyy'.

maybe oracle does not allow the use of the column alias in the GROUP BY clause

try repeating the function expression in the GROUP BY clause

and if it doesn't work, please show the exact query you ran

[venkat6134]

This is the query i run:

SQL> select date_format(date_of_credit,'%b-%y'),
sum(tot_01) as sum_tot01
from challan_details
inner join
master on master.code=challan_details.code where master.distcode in ('A','B') group by date_format(date_of_credit,'%b-%y');


*
ERROR at line 1:
ORA-00904: "DATE_FORMAT": invalid identifier

[r937]

thanks, i got for month with to_char.

why did you give up this solution and go back to the mysql function?

[venkat6134]

Thank you,
with out to_char, is there any date function for specifying the date formats as user friendly.?

[r937]

Thank you,
with out to_char, is there any date function for specifying the date formats as user friendly.?

sorry, i am not an oracle user... please consult your oracle manual