Click here to Skip to main content
15,903,175 members
Please Sign up or sign in to vote.
1.00/5 (2 votes)
See more:
I have this code to get the sum of each product from different tables and multiply by price to get the total for each row in columns. However, I want to calculate rows to be in one row in one sentence.

could you please help me?

What I have tried:

SQL
SELECT FORMAT((((ISNULL(IT.Q,0) + 
  (SELECT ISNULL(SUM(IM.Q), 0) 
   FROM TBL_I_IT AS IM 
   WHERE IT.N = IM.N) + 
  (SELECT ISNULL(SUM(RE.Q), 0)
   FROM TBL_R_IT AS RE
   WHERE IT.N = RE.N)) - 
  ((SELECT ISNULL(SUM(EX.Q), 0) 
    FROM TBL_E_IT AS EX
    WHERE IT.N = EX.N))) * IT.BP), 
  'N4') AS 'T' 
FROM TBL_IT AS IT
Posted
Updated 1-Mar-23 5:12am
v5
Comments
OriginalGriff 1-Mar-23 3:46am    
This is not a good question - we cannot work out from that little what you are trying to do.
Remember that we can't see your screen, access your HDD, or read your mind - we only get exactly what you type to work with - we get no other context for your project.
Imagine this: you go for a drive in the country, but you have a problem with the car. You call the garage, say "it broke" and turn off your phone. How long will you be waiting before the garage arrives with the right bits and tools to fix the car given they don't know what make or model it is, who you are, what happened when it all went wrong, or even where you are?

That's what you've done here. So stop typing as little as possible and try explaining things to people who have no way to access your project!

Perhaps sample input and output data would help us understand you better?

Use the "Improve question" widget to edit your question and provide better information.
CHill60 1-Mar-23 7:30am    
There is no need for all those sub-queries - see SQL server performance - Death by correlated subqueries - SQL Service[^]
You are not returning a sum, you are returning a String (FORMAT) - leave the formatting to the UI layer
Provide some sample data for each of the tables and your expected results.
Finally - you appear to have multiple tables with identical schema - that is bad database design. You should also use more meaningful column names

1 solution

Seems, that your tables are related each other... Then, you should use JOINS[^]!

SQL
SELECT FORMAT(ISNULL(IT.Q,0) + 
  ISNULL(SUM(IM.Q), 0) +
  ISNULL(SUM(RE.Q), 0) -
  (ISNULL(SUM(EX.Q), 0) * IT.BP), 'N4') AS TOTAL
FROM TBL_IT AS IT
INNER JOIN TBL_I_IT AS IM 
  ON IT.N = IM.N
INNER JOIN TBL_R_IT AS RE
  ON IT.N = RE.N
INNER JOIN TBL_E_IT AS EX
  ON IT.N = EX.N;


If you want to get the same calculations for "each row", then use a "key", which is - in your case - a "N" field.

SQL
SELECT IT.N, FORMAT(ISNULL(IT.Q,0) + 
  ISNULL(SUM(IM.Q), 0) +
  ISNULL(SUM(RE.Q), 0) -
  (ISNULL(SUM(EX.Q), 0) * IT.BP), 'N4') AS TOTAL
FROM TBL_IT AS IT
INNER JOIN TBL_I_IT AS IM 
  ON IT.N = IM.N
INNER JOIN TBL_R_IT AS RE
  ON IT.N = RE.N
INNER JOIN TBL_E_IT AS EX
  ON IT.N = EX.N
GROUP BY IT.N;


For further details, please see: SQL - Group By[^]
 
Share this answer
 
v2
Comments
Karam Ibrahim 21-Mar-23 7:48am    
Thanks, dear Maciej Los, that I want to. I appreciate your helping me.
Maciej Los 21-Mar-23 11:09am    
You're very welcome.
Good luck!
Karam Ibrahim 28-Mar-23 4:51am    
Thanks Dear

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900