[Mr.President]

Database Query :




Database Query result:




SQL

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

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 ?

[shabbir]

What you are trying to get?

[Mr.President]

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

[shabbir]

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.

[nimesh]

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.

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.

[Mr.President]

Quote:

Originally Posted by nimesh

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.

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.

thx that query worked !!