SitePoint Sponsor

User Tag List

Results 1 to 13 of 13
  1. #1
    SitePoint Evangelist venkat6134's Avatar
    Join Date
    Jul 2010
    Posts
    454
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    total and group by with where clause in sub query?

    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.

  2. #2
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,401
    Mentioned
    147 Post(s)
    Tagged
    4 Thread(s)
    What is the problem you're having?

  3. #3
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,015
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    Quote Originally Posted by venkat6134 View Post
    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
    Code:
    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
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  4. #4
    SitePoint Evangelist venkat6134's Avatar
    Join Date
    Jul 2010
    Posts
    454
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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.

  5. #5
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,015
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    look up TO_CHAR in your manual
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  6. #6
    SitePoint Evangelist venkat6134's Avatar
    Join Date
    Jul 2010
    Posts
    454
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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 ?

  7. #7
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,015
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    Quote Originally Posted by venkat6134 View Post
    Now i want the monthlywise totals.
    see post #3
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  8. #8
    SitePoint Evangelist venkat6134's Avatar
    Join Date
    Jul 2010
    Posts
    454
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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...

  9. #9
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,015
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    Quote Originally Posted by venkat6134 View Post
    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
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  10. #10
    SitePoint Evangelist venkat6134's Avatar
    Join Date
    Jul 2010
    Posts
    454
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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

  11. #11
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,015
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    Quote Originally Posted by venkat6134 View Post
    thanks, i got for month with to_char.
    why did you give up this solution and go back to the mysql function?
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  12. #12
    SitePoint Evangelist venkat6134's Avatar
    Join Date
    Jul 2010
    Posts
    454
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thank you,
    with out to_char, is there any date function for specifying the date formats as user friendly.?

  13. #13
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,015
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    Quote Originally Posted by venkat6134 View Post
    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
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •