Error SQL syntax on FROM and JOIN

Question

Can anyone point me in the right direction why this sql query got an error?

http://sqlfiddle.com/#!9/40058/2

Expected results is to

get the names of Product on a table,

the count of sales on that product,

The amount (transactionamount) and the net amount (statementdebit)

transaction is a keyword. you should change your table name — Badiparmagi
– Badiparmagi, Commented Jan 18, 2018 at 5:42
You missed one closing backet in sum statement and remove the spaces after functions like COUNT() and SUM(). — Punabaka Abhinav
– Punabaka Abhinav, Commented Jan 18, 2018 at 5:48
Add all relevant Informationen in SO. Not only in a link to sqlfiddle. — Jens
– Jens, Commented Jan 18, 2018 at 6:27

Pankaj Gadge · Accepted Answer · 2018-01-18 05:43:50Z

4

Couple of things wrong in the query

Closing parentheses missing on SUM(transaction.transactionamount AS Amount
Extra spaces after aggregate functions on select e.g. COUNT ()

Here's the working SQL

SELECT 
DISTINCT transaction.transactionservicetype AS Product,
COUNT(transaction.transactionid) AS Count2,
SUM(transaction.transactionamount) AS Amount,
SUM(statement.statementdebit) AS NetCost
FROM transaction 
RIGHT JOIN statement ON transaction.transactionid = statement.transactionid
WHERE transaction.transactiondate = '2018-01-17' AND transaction.transactionservicetype = 'LBread';

Working SQL Fiddle

answered Jan 18, 2018 at 5:43

Pankaj Gadge

2,8443 gold badges21 silver badges25 bronze badges

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

Comments

Vikram Jain · Accepted Answer · 2018-01-18 05:55:04Z

1

Missing Closing parentheses on "SUM(transaction.transactionamount AS Amount" and 
Extra Spaces between COUNT(),SUM() function like "SUM (transaction.transactionamount):

Updated query as per  below using left outer join and 
SQL-Fiddle is "http://sqlfiddle.com/#!9/40058/92" : 

SELECT 
T.transactionservicetype AS Product,
COUNT(T.transactionid) AS CountTotal,
SUM(T.transactionamount) AS Amount,
SUM(S.statementdebit) AS NetCost 
FROM statement S  
left outer join transaction T  ON T.transactionid = S.transactionid
WHERE T.transactiondate = '2018-01-17' AND T.transactionservicetype = 'LBread'
group by T.transactionservicetype;

answered Jan 18, 2018 at 5:55

Vikram Jain

5,6081 gold badge22 silver badges31 bronze badges

Comments

Prateik Darji · Accepted Answer · 2018-01-18 06:01:27Z

1

a transaction is a keyword please cover it with backtick transaction

you are missing one parenthesis after transaction.transactionamount

another thing COUNT (), SUM () should be like COUNT(),SUM() no extra spaces required in function.

SELECT 
DISTINCT `transaction`.transactionservicetype AS Product,
count(`transaction`.transactionid) AS Count,
sum(`transaction`.transactionamount) AS Amount,
sum(statement.statementdebit) AS NetCost
FROM `transaction` 
RIGHT JOIN statement ON `transaction`.transactionid = 
statement.transactionid
WHERE `transaction`.transactiondate = '2018-01-17' AND 
`transaction`.transactionservicetype = 'LBread';

edited Jan 18, 2018 at 6:01

answered Jan 18, 2018 at 5:45

Prateik Darji

2,3071 gold badge15 silver badges29 bronze badges

Comments

Hasan · Accepted Answer · 2018-01-18 07:25:02Z

0

hope it will help you, i'm bit confuse because your table naming field naming.

this my solution http://sqlfiddle.com/#!9/40058/83/0

sql query :

    SELECT sum(a.statementdebit) debit,count(a.statementdesc) tot_prod,
sum(b.transactionamount) amount,a.statementdesc
FROM 
statement a
JOIN transaction b on a.transactionid=b.transactionid
where b.transactiondate = '2018-01-17'
group by a.statementdesc

edited Jan 18, 2018 at 7:25

answered Jan 18, 2018 at 5:52

Hasan

324 bronze badges

Collectives™ on Stack Overflow

Error SQL syntax on FROM and JOIN

4 Answers 4

Comments

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related