rails 3: trying to COUNT/GROUP on multiple fields, but getting SQL error

Question

My table StoreCodes holds unique products codes (and their availablity) for each store, eg:

category:integer
code:string
available:boolean
store_id:reference

I'm trying to show a simple table with 3 columns showing how many codes are in each category, AND how many of those codes have available = true

Category_number / Count_Codes_in_category / Count_Codes_in_category_available==TRUE

In my controller, the following attempt to COUNT and GROUP on both category and available throws this error:

SQLite3::SQLException: near "WHERE": syntax error: SELECT category, available, COUNT(*) AS instances GROUP BY category, available WHERE (store_id = 8)

My controller code:

@b2 = StoreCode.find_by_sql ['SELECT category, available, COUNT(*) AS instances GROUP BY category, available WHERE (store_id = ?)', @store.id]

Jimmy Stenke · Accepted Answer · 2011-08-22 19:17:27Z

1

Your SQL is wrong. You have to put the GROUP BY clause after the WHERE clause, and when using find_by_sql you also need to write the complete statement (include the FROMclause)

Like this:

@b2 = StoreCode.find_by_sql ['SELECT category, available, COUNT(*) AS instances 
FROM store_codes WHERE (store_id = ?) GROUP BY category, available', @store.id]

answered Aug 22, 2011 at 19:17

Jimmy Stenke

11.3k2 gold badges27 silver badges20 bronze badges

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

Comments

ipr101 · Accepted Answer · 2011-08-22 19:16:50Z

1

Your SQL statement has no 'FROM' clause, I also think the 'GROUP BY' should come after the 'WHERE'.

answered Aug 22, 2011 at 19:16

ipr101

24.2k8 gold badges61 silver badges62 bronze badges

Collectives™ on Stack Overflow

rails 3: trying to COUNT/GROUP on multiple fields, but getting SQL error

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related