CODEWITHSUNDEEP
sqlsql-serversql-server-2008
prers
prers

Reputation: 99

why is it giving incorrect syntax?

I have made 3 tables in SQL server named t_user_master,t_product_master and t_transaction.The t_user_master is linked with t_transaction through the columns Users_id and t _product_master is linked with t_transaction though the columns Products_id.Now I have to generate and output which consists of users_name(from t_user_master),Product_name(from t_product_master) linked with it so as to see which used ordered which product as so on. Here is a snippet of my code-

 SELECT um.Users_Name,
       pm.Product_Name,
       (SELECT SUM(Transaction_Amount)
        FROM   t_transaction
        WHERE  Transaction_Type = 'Order'
        GROUP  BY Users_Id,
                  Product_Id) AS Ordered_quantity,
       (SELECT SUM(Transaction_Amount)
        FROM   t_transaction
        WHERE  Transaction_Type = 'Payment'
        GROUP  BY Users_ID,
                  Product_Id) AS Amount_Paid,
       (SELECT MAX(Transaction_Date)
        FROM   t_transaction
        GROUP  BY Users_Id,
                  Product_Id) AS Last_Transaction_Date,
       (SELECT ( ( SUM(Transaction_Amount) * pm.Cost_per_Item ) - SUM(Transaction_Amount) ) )AS Balance
        FROM   t_user_master um
               JOIN t_transaction tr
                 ON um.Users_ID = tr.Users_ID
               JOIN t_product_master pm
                 ON tr.Product_ID = pm.Product_ID
    GROUP BY um.Users_Name,pm.Product_Name

I have recitified all the errors related to syntax but now I am facing another problem.After executing it I am getting this message -Column 't_product_master.Cost_Per_Item' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause. Where am i going wrong?

Upvotes: 1

Views: 708

Answers (4)

Stacky
Stacky

Reputation: 905

You missed a ")" at the end of the 10th line and a "," after Last_Transaction_Date. Try this:

SELECT    um.Users_Name,pm.Product_Name,
(SELECT   SUM(Transaction_Amount)
 FROM     t_transaction 
 WHERE    Transaction_Type='Order' 
 GROUP BY Users_Id,Product_Id
) AS Ordered_quantity,
(SELECT   SUM(Transaction_Amount)
 FROM     t_transaction 
 WHERE    Transaction_Type='Payment'
 GROUP BY Users_ID,Product_Id
) AS Amount_Paid,
(SELECT   MAX(Transaction_Date)
 FROM     t_transaction 
 GROUP BY Users_Id,Product_Id
) AS Last_Transaction_Date,
(SELECT   ( ( SUM(Transaction_Amount)*pm.Cost_per_Item ) - SUM(Transaction_Amount) )
) AS Balance
FROM      t_user_master um 
JOIN      t_transaction tr ON um.Users_ID=tr.Users_ID
JOIN      t_product_master pm ON tr.Product_ID = pm.Product_ID

GROUP BY um.Users_Name,pm.Product_Name

Upvotes: 1

Deepshikha
Deepshikha

Reputation: 10264

There are actually three issues in your query:

  1. A missing comma after Last_Transaction_Date.
  2. Ending bracket while selecting Balance.
  3. When calculating balance you have used pm.Cost_per_Item. Once you do GROUP BY all the select statement columns are calculated on resulting grouped rows. I have used sum but you can use any other aggregate function as required.

Correct query is:

SELECT um.Users_Name,
    pm.Product_Name,
    (SELECT SUM(Transaction_Amount) FROM t_transaction 
    WHERE Transaction_Type='Order' 
    GROUP BY Users_Id,Product_Id) AS Ordered_quantity,
    (SELECT SUM(Transaction_Amount) FROM t_transaction 
    WHERE Transaction_Type='Payment'
    GROUP BY Users_ID,Product_Id )AS Amount_Paid,
    (SELECT MAX(Transaction_Date) FROM t_transaction 
      GROUP BY Users_Id,Product_Id) AS Last_Transaction_Date, -- error 1
     (SELECT ((SUM(Transaction_Amount)* sum(pm.Cost_per_Item))-SUM(Transaction_Amount))) AS 
      Balance -- error 2 and error 3
FROM t_user_master um 
JOIN t_transaction tr ON um.Users_ID=tr.Users_ID
JOIN t_product_master pm ON tr.Product_ID = pm.Product_ID
GROUP BY um.Users_Name,pm.Product_Name ;

Hope this helps!!!

Upvotes: 0

Jazzy J
Jazzy J

Reputation: 311

You're missing a comma after LastTransactionDate and the last column of your SELECT looks to be malformed, i.e.

(SELECT ((SUM(Transaction_Amount)*pm.Cost_per_Item)-SUM(Transaction_Amount)) AS Balance

Upvotes: 0

oooo ooo
oooo ooo

Reputation: 314

At the very least you're missing a "," at the end of this line:

GROUP BY Users_Id,Product_Id) AS Last_Transaction_Date

Upvotes: 0

Related Questions