Nested query in sql server

Question

Hello then the query below performs a select on a sql server database, the problem is that the total field is calculated as the total of all the tables, not of each , how can I run the account individually?Each table has a construction field and I want to calculate the Totale field based on that field

Query:

SELECT *
FROM ((SELECT Cantiere.IdCantiere,
              Cliente.RagioneSociale,
              'Preventivo' AS Tipo,
              Cantiere.NomeCantiere,
              Cantiere.StatoCantiere,
              (SELECT (SELECT COALESCE(SUM(Prezzo * Quantita), 0)FROM ArticoloCantiere ) + (SELECT COALESCE(SUM((Kilometri.Kilometri * CostoKilometrico) + DirittoChiamata), 0)
                                                                                           FROM Kilometri ) + (SELECT COALESCE(SUM(CostoNoleggio + Trasporto), 0)FROM Noleggio) + (SELECT COALESCE(SUM(Utente.CostoInterno * ((REPLACE((LEFT(CAST(REPLACE((CONVERT(varchar(5), CAST(CONVERT(varchar(5), (RisorseUmane.OreFine - RisorseUmane.OreInizio), 108) AS datetime) - CAST(REPLACE(RisorseUmane.Pausa, '.', ':') AS datetime), 108)), ':', ',') AS money) / 100, 2)), '.', '')) + (((CAST('0,' + (RIGHT(CAST(REPLACE((CONVERT(varchar(5), CAST(CONVERT(varchar(5), (RisorseUmane.OreFine - RisorseUmane.OreInizio), 108) AS datetime) - CAST(REPLACE(RisorseUmane.Pausa, '.', ':') AS datetime), 108)), ':', ',') AS money) / 100, 2)) AS money) / 100) * 100) / 60))), 0)
                                                                                                                                                                                  FROM RisorseUmane
                                                                                                                                                                                       INNER JOIN Utente ON RisorseUmane.IdUtente = Utente.IdUtente where IdCantiere=@IdCantiere) + (SELECT COALESCE(SUM(Costo), 0)FROM SpeseSostenute where IdCantiere=@IdCantiere) + (SELECT COALESCE(SUM(Costo), 0)FROM Ristorante )) AS Totale
       FROM Cantiere
            INNER JOIN Cliente ON Cantiere.IdCliente = Cliente.IdCliente
            INNER JOIN CantierePreventivo ON CantierePreventivo.IdCantierePreventivo = Cantiere.IdCantiere)
      UNION
      (SELECT Cantiere.IdCantiere,
              Cliente.RagioneSociale,
              'Consuntivo' AS Tipo,
              Cantiere.NomeCantiere,
              Cantiere.StatoCantiere,
              (SELECT (SELECT COALESCE(SUM(Prezzo * Quantita), 0)FROM ArticoloCantiere ) + (SELECT COALESCE(SUM((Kilometri.Kilometri * CostoKilometrico ) + DirittoChiamata), 0)
                                                                                           FROM Kilometri ) + (SELECT COALESCE(SUM(CostoNoleggio + Trasporto), 0)FROM Noleggio ) + (SELECT COALESCE(SUM(Utente.CostoInterno * ((REPLACE((LEFT(CAST(REPLACE((CONVERT(varchar(5), CAST(CONVERT(varchar(5), (RisorseUmane.OreFine - RisorseUmane.OreInizio), 108) AS datetime) - CAST(REPLACE(RisorseUmane.Pausa, '.', ':') AS datetime), 108)), ':', ',') AS money) / 100, 2)), '.', '')) + (((CAST('0,' + (RIGHT(CAST(REPLACE((CONVERT(varchar(5), CAST(CONVERT(varchar(5), (RisorseUmane.OreFine - RisorseUmane.OreInizio), 108) AS datetime) - CAST(REPLACE(RisorseUmane.Pausa, '.', ':') AS datetime), 108)), ':', ',') AS money) / 100, 2)) AS money) / 100) * 100) / 60))), 0)
                                                                                                                                                                                  FROM RisorseUmane
                                                                                                                                                                                       INNER JOIN Utente ON RisorseUmane.IdUtente = Utente.IdUtente ) + (SELECT COALESCE(SUM(Costo), 0)FROM SpeseSostenute ) + (SELECT COALESCE(SUM(Costo), 0)FROM Ristorante )) AS Totale
       FROM Cantiere
            INNER JOIN Cliente ON Cantiere.IdCliente = Cliente.IdCliente
            INNER JOIN CantiereConsuntivo ON CantiereConsuntivo.IdCantiereConsuntivo = Cantiere.IdCantiere)) q
ORDER BY q.Totale DESC;

Consumable sample data, and expected results will greatly help us, help you. — Thom A, Aug 27 '19 at 08:00
Please update your question and copy/paste the error text/message, Errors in images are not allowed in Stackoverflow : https://meta.stackoverflow.com/questions/285551/why-not-upload-images-of-code-on-so-when-asking-a-question — Fourat, Aug 27 '19 at 08:34

score 0 · Answer 1 · answered Aug 27 '19 at 08:30

0

your totale column in all of tables is same. you must calculate total in each section by joining total result of those section with associated Cantiere row. for more help please give your tables structure and sample data.

answered Aug 27 '19 at 08:30

Afshin Rashidi

343
2
10

Nested query in sql server

1 Answers1