Go4Expert

Go4Expert (http://www.go4expert.com/)
-   Database (http://www.go4expert.com/forumdisplay.php?f=24)
-   -   SQL group by query problem (http://www.go4expert.com/showthread.php?t=22863)

Mr.President 29Jul2010 19:42
SQL group by query problem
 
Database Query :

http://img822.imageshack.us/img822/8...eenshot1tv.jpg


Database Query result:

http://img137.imageshack.us/img137/6...reenshot2l.jpg


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 ?

shabbir 29Jul2010 19:53
Re: SQL group by query problem
 
What you are trying to get?

Mr.President 29Jul2010 21:08
Re: SQL group by query problem
 
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 29Jul2010 21:22
Re: SQL group by query problem
 
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 2Aug2010 10:22
Re: SQL group by query problem
 
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 2Aug2010 10:25
Re: SQL group by query problem
 
Quote:
Originally Posted by nimesh (Post 71055)
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 !!


All times are GMT +5.5. The time now is 03:47.