1. We have moved from vBulletin to XenForo and you are viewing the site in the middle of the move. Though the functional aspect of everything is working fine, we are still working on other changes including the new design on Xenforo.
    Dismiss Notice

SQL group by query problem

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

  1. Mr.President

    Mr.President New Member

    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

    What you are trying to get?
     
  3. Mr.President

    Mr.President New Member

    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

    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

    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

    thx that query worked !!
     

Share This Page