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
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 ?
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.
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