1. This site uses cookies. By continuing to use this site, you are agreeing to our use of cookies. Learn More.

SQL group by query problem

Discussion in 'Database' started by Mr.President, Jul 29, 2010.

  1. Mr.President

    Mr.President New Member

    Joined:
    Apr 25, 2010
    Messages:
    51
    Likes Received:
    1
    Trophy Points:
    0
    Database Query :

    [​IMG]


    Database Query result:

    [​IMG]


    SQL

    Code:
    SELECT Invoice.iid, Invoice.idate, customer.cname, rep.sfname, invoice_stock.qty, invoice_stock.selling
    FROM rep INNER JOIN ((customer INNER JOIN Invoice ON customer.cid = Invoice.cid) INNER JOIN invoice_stock ON Invoice.iid = invoice_stock.iid) ON rep.repid = customer.repid;

    MY SQL code with GROUP BY clause

    Code:
    SELECT Invoice.iid, Invoice.idate, customer.cname, rep.sfname,SUM( invoice_stock.qty* invoice_stock.selling)
    FROM rep INNER JOIN ((customer INNER JOIN Invoice ON customer.cid =  Invoice.cid) INNER JOIN invoice_stock ON Invoice.iid =  invoice_stock.iid) ON rep.repid = customer.repid
    GROUP BY Invoice.iid, Invoice.idate;
    ;

    can anyone point me out the error which I made ?
     
  2. shabbir

    shabbir Administrator Staff Member

    Joined:
    Jul 12, 2004
    Messages:
    15,287
    Likes Received:
    364
    Trophy Points:
    83
    What you are trying to get?
     
  3. Mr.President

    Mr.President New Member

    Joined:
    Apr 25, 2010
    Messages:
    51
    Likes Received:
    1
    Trophy Points:
    0
    what I am trying to get is some details of invoice it should look like this

    Invoice ID || Invoice DATE || Customer Name || Salesman Name || Invoice TOTAL
     
  4. shabbir

    shabbir Administrator Staff Member

    Joined:
    Jul 12, 2004
    Messages:
    15,287
    Likes Received:
    364
    Trophy Points:
    83
    And I see that you are getting the same as well. What error you are seeing the input. Have you checked if the date is only date and not date as well as time.
     
  5. nimesh

    nimesh New Member

    Joined:
    Apr 13, 2009
    Messages:
    770
    Likes Received:
    20
    Trophy Points:
    0
    Occupation:
    Oracle Apps Admin
    Location:
    Mumbai
    Home Page:
    In the code for Group by, there are 5 columns including aggregate SUM, but the group by statement has only four columns which is incorrect. it should be 1 less than actual.

    Try the below code.

    Code:
    SELECT Invoice.iid, Invoice.idate, customer.cname, rep.sfname,SUM( invoice_stock.qty* invoice_stock.selling)
    FROM rep INNER JOIN ((customer INNER JOIN Invoice ON customer.cid =  Invoice.cid) INNER JOIN invoice_stock ON Invoice.iid =  invoice_stock.iid) ON rep.repid = customer.repid
    GROUP BY Invoice.iid, Invoice.idate, customer.cname, rep.sfname;
    ;
    And do share the error that you are receiving.
     
  6. Mr.President

    Mr.President New Member

    Joined:
    Apr 25, 2010
    Messages:
    51
    Likes Received:
    1
    Trophy Points:
    0
    thx that query worked !!
     

Share This Page