venkat6134 — 2013-02-28T06:24:35-05:00 — #1
i have two tables. Tab1, Tab2.
Tab1 having columns as,
Tab2 having columns as:
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.
guido2004 — 2013-02-28T08:31:39-05:00 — #2
What is the problem you're having?
r937 — 2013-02-28T09:11:48-05:00 — #3
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
ON tab2.itemno = tab1.itemno
WHERE tab1.distcode IN ( 'A','B' )
if either of my assumptions is not true, you will ahve to change the first line of the SELECT
venkat6134 — 2013-02-28T23:55:08-05:00 — #4
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 — 2013-03-01T01:56:12-05:00 — #5
look up TO_CHAR in your manual
venkat6134 — 2013-03-01T05:35:59-05:00 — #6
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 — 2013-03-01T06:56:11-05:00 — #7
see post #3
venkat6134 — 2013-03-04T04:52:42-05:00 — #8
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.
r937 — 2013-03-04T05:09:46-05:00 — #9
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 — 2013-03-04T05:47:22-05:00 — #10
This is the query i run:
SQL> select date_format(date_of_credit,'%b-%y'),
sum(tot_01) as sum_tot01
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 — 2013-03-04T06:14:04-05:00 — #11
why did you give up this solution and go back to the mysql function?
venkat6134 — 2013-03-04T06:24:55-05:00 — #12
with out to_char, is there any date function for specifying the date formats as user friendly.?
r937 — 2013-03-04T06:47:35-05:00 — #13
sorry, i am not an oracle user... please consult your oracle manual