Sum function inside sum function

Question

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 28-Feb-23 20:06pm

Karam Ibrahim

Updated 1-Mar-23 5:12am

Maciej Los

v5

Add a Solution

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

Add a Solution

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

Maciej Los · Accepted Answer · 2023-03-01T05:12:00

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[^]

Sum function inside sum function

1 solution

Solution 1

Add your solution here

Preview 0

Sum function inside sum function

1 solution

Solution 1

Add your solution here

Preview 0

Existing Members

...or Join us