"PG::SyntaxError: ERROR" When attemoting to create "record-specific charts"

Help me!
1

Hello,

I am trying to create an analytics transactions chart per specific company, and I tried to follow the example SQL in this documentation [1].

Ours is a little different in that: card_transaction belongs_to card, card belongs_to user, and user belongs_to company. Here is my SQL:

SELECT DATE_TRUNC('day', card_transactions.created_at) AS key, SUM(card_transactions.amount) AS value
FROM card_transactions
JOIN cards ON cards.id = card_transactions.card_id
JOIN users ON users.id = cards.user_id
JOIN companies ON companies.id = users.company_id
WHERE companies.id = ?
GROUP BY key
ORDER BY key;

And I get this error after saving:

PG::SyntaxError: ERROR: syntax error at or near "GROUP" LINE 7: GROUP BY key ^

[1] Display record-specific charts in Analytics - Documentation

Thanks!

2

Hey @muz

Could you please share your project name so I can see if I can spot anything?

Also, do you confirm that this chart is created on the Analytics tabs?

Thanks in advance

1 Like
3

Hi @jeffladiray,

Thanks for getting back to me and for asking if I was creating the chart in the Analytics tab, which I wasn’t.

So I went ahead and created a new chart in the Company Analytics tab, and it worked!

Here is the SQL for a weekly transaction scoped by company_id:

SELECT DATE_TRUNC('week', card_transactions.created_at) AS key, SUM(card_transactions.amount) / 100 AS value
FROM card_transactions
JOIN cards ON cards.id = card_transactions.card_id
JOIN users ON users.id = cards.user_id
JOIN companies ON companies.id = users.company_id
WHERE companies.id = ?
GROUP BY key
ORDER BY key;

As always, thank you so much for your help, and I apologize for missing such an important detail in the first place

Have a great weekend!

2 Likes