Creating a Where Clause in mysql

Question

find_by_sql("SELECT s.productcode, s.description, CAST(SUM(l.amount) as UNSIGNED) AS amount FROM softwares s LEFT JOIN licenses l ON s.id=l.software_id GROUP BY s.productcode, s.description WHERE s.supplier= 'softcat'")

I currently have this, and i get the error ActiveRecord::StatementInvalid in SoftwaresController#export_softcat

I'm trying to add a where clause on the end to only select the records with a certain supplier.

F.P · Accepted Answer · 2012-08-22 11:11:26Z

3

SELECT s.productcode, s.description, CAST(SUM(l.amount) as UNSIGNED) AS amount
FROM softwares s
    LEFT JOIN licenses l ON s.id=l.software_id
WHERE s.supplier = 'softcat'
GROUP BY s.productcode, s.description

Try it this way

answered Aug 22, 2012 at 11:11

F.P

18k34 gold badges127 silver badges196 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Romain · Accepted Answer · 2012-08-22 11:11:10Z

2

You'll need to have the WHERE clause before the GROUP BY clause, effectively changing the SQL to be:

SELECT s.productcode, s.description, CAST(SUM(l.amount) as UNSIGNED) AS amount
FROM softwares s 
LEFT JOIN licenses l ON s.id=l.software_id
WHERE s.supplier= 'softcat'
GROUP BY s.productcode, s.description

answered Aug 22, 2012 at 11:11

Romain

12.9k3 gold badges44 silver badges54 bronze badges

Comments

MimiEAM · Accepted Answer · 2012-08-22 11:12:21Z

2

You have to put the GROUP BY statement after the WHERE clause statement

answered Aug 22, 2012 at 11:12

MimiEAM

2,6401 gold badge27 silver badges29 bronze badges

Collectives™ on Stack Overflow

Creating a Where Clause in mysql

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related