SQL group by query problem

Mr.President's Avatar, Join Date: Apr 2010
Contributor
Database Query :




Database Query result:




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 ?
0
shabbir's Avatar, Join Date: Jul 2004
Go4Expert Founder
What you are trying to get?
0
Mr.President's Avatar, Join Date: Apr 2010
Contributor
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
0
shabbir's Avatar, Join Date: Jul 2004
Go4Expert Founder
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.
0
nimesh's Avatar, Join Date: Apr 2009
Invasive contributor
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.
Mr.President, shabbir likes this
0
Mr.President's Avatar, Join Date: Apr 2010
Contributor
Quote:
Originally Posted by nimesh View Post
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.
thx that query worked !!
0
Camron70's Avatar, Join Date: Oct 2014
Newbie Member
a nice post is made i like....



_________________
watson