sql syntax or access voilation

Question

My MySql function keep on giving me syntax error message:

SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in 
 your SQL syntax; check the manual that corresponds to your MySQL server version 
 for the right syntax to use near 'SELECT SUM(sell_rate) FROM (SELECT 
 COUNT(stock_id)*stock_rate sell_rate 
 FRO' at line 2 (SQL: select 
 SELECT SUM(sell_rate) FROM (SELECT COUNT(stock_id)*stock_rate sell_rate 
 FROM tbl_stock st 
 INNER JOIN tbl_product pdt ON pdt.product_id = st.produck_id 
 WHERE produck_name = 'product_id' lIMIT 6
 GROUP BY stock_id)

  from `tbl_stock`)

I have checked all my brackets and they are all closed i have even executed this on MySQL workbench and it is fine but in Laravel it gives me that error here is my function

private function totalprice( $product_id)
   {
   $selltotal = DB::table('tbl_stock')
   ->select(DB::raw("
    SELECT SUM(sell_rate) FROM (SELECT COUNT(stock_id)*stock_rate sell_rate 
    FROM tbl_stock st 
    INNER JOIN tbl_product pdt ON pdt.product_id = st.produck_id 
        WHERE produck_name = 'product_id' lIMIT 6
    GROUP BY stock_id)

    "))->get();
  return $selltotal;
   }

What is your query? Are you sure your account has `SELECT` access? — tadman, Jul 31 '17 at 16:32
Looks like you're missing a comma between `stock_rate` and `sell_rate` — aynber, Jul 31 '17 at 16:34
`->select` already does a `select`. Then you add a raw part that contains another `select`, so you end up with `select select sum(...`. — Solarflare, Jul 31 '17 at 16:36
@Andrew I think he is saying you don't need to include "SELECT" in your query text. This is why I don't understand why people use these kinds of frameworks; if they require you to end up writing fragmented sql anyway, why not write the whole query yourself. — Uueerdo, Jul 31 '17 at 16:47

CoursesWeb · Answer 1 · 2017-07-31T17:30:17.280

0

Try with DB::selet():

$selltotal = DB::select("SELECT SUM(sell_rate) FROM (SELECT COUNT(stock_id)*stock_rate sell_rate 
FROM tbl_stock st 
INNER JOIN tbl_product pdt ON pdt.product_id = st.produck_id 
  WHERE produck_name = 'product_id' lIMIT 6)
GROUP BY stock_id");

edited Jul 31 '17 at 17:30

answered Jul 31 '17 at 16:40

CoursesWeb

4,179
3
21
27

with this solution i get this error /* check the manual that corresponds to your MySQL server version for the right syntax to use near 'GROUP BY stock_id)' at line 5*/ and when i remove the LIMIT 6 STATEMENT I GET Every derived table must have its own alias (SQL: SELECT SUM(sell_rate) FROM (SELECT COUNT(stock_id)*stock_rate sell_rate FROM tbl_stock st INNER JOIN tbl_product pdt ON pdt.product_id = st.produck_id WHERE produck_name = 'product_id' GROUP BY stock_id)) – Andrew Jul 31 '17 at 16:47
Sorry, not know how to use GROUP BY and LIMIT. Maybe you can find a solution here: https://stackoverflow.com/questions/2129693/using-limit-within-group-by-to-get-n-results-per-group . Try first to test the SQL in PhpMyAdmin. – CoursesWeb Aug 02 '17 at 16:19
Try this SQL (without LIMIT): `SELECT SUM(tb1.sell_rate) FROM (SELECT COUNT(stock_id)*stock_rate*sell_rate FROM tbl_stock st INNER JOIN tbl_product pdt ON pdt.product_id = st.produck_id WHERE produck_name = 'product_id' GROUP BY stock_id) as tb1` ; Anyway it's to complicated query for me. – CoursesWeb Aug 02 '17 at 16:23

score -1 · Answer 2 · answered Jul 31 '17 at 17:26

Instead of using SELECT, try just a DB::statement. You're also missing a closing parenthesis that should be after LIMIT 6

$selltotal = DB::statement("SELECT SUM(sell_rate)
      FROM
          (SELECT COUNT(stock_id) * stock_rate sell_rate
           FROM tbl_stock st
             INNER JOIN tbl_product pdt ON pdt.product_id = st.produck_id
           WHERE produck_name = 'product_id'
           LIMIT 6)
        GROUP BY stock_id");

sql syntax or access voilation

2 Answers2