How to compose this SQL Server query?

Question

I studied some SQL but I forgot everything and now I'm trying to do this database schema:

enter image description here

And I want to subtract of (all INGRESADO - all CANTIDAD) and return a unique value, something like this:

SELECT SUM(ingresado) - SUM(cantidad) FROM Nomina, Gasto;

But I think, it's not returning the real value I want...

Thanks!

EDIT:

DECLARE @nomina FLOAT;
DECLARE @gasto FLOAT;
SELECT @nomina = SUM(ingresado) FROM Nomina;
SELECT @gasto = SUM(cantidad) FROM Gasto;
SELECT @nomina - @gasto;

This return the value I want, but I want it in a unique query, THANKS.

How do the 2 tables relate? I can't tell from the field names above. — sgeddes, Feb 16 '13 at 14:19
I don't need to relate those tables so... I think it's fine like this, I wanted to make this application so simple in order to have to improve later, this is for my personal purpouse and I want to edit it later, and probabbly I will relate it and add new tables, etc... — Alejandro L., Feb 16 '13 at 14:33

score 3 · Answer 1 · answered Feb 16 '13 at 14:19

3

Try this:

SELECT(SELECT SUM(ingresado) FROM Nomina - SELECT SUM(cantidad) FROM Gasto)

answered Feb 16 '13 at 14:19

Hamlet Hakobyan

32,965
6
52
68

1

This is correct, but It should be `SELECT ((SELECT SUM(ingresado) FROM Nomina) - (SELECT SUM(cantidad) FROM Gasto));` Thanks =) – Alejandro L. Feb 16 '13 at 14:23

score 3 · Accepted Answer · answered Feb 16 '13 at 14:22

3

SELECT ( SELECT SUM(ingresado) FROM Nomina ) - ( SELECT SUM(cantidad) FROM Gasto ) as Delta

answered Feb 16 '13 at 14:22

HABO

15,314
5
39
57

score 2 · Answer 3 · edited May 23 '17 at 11:49

2

Select (SELECT SUM(ingresado) from Nomina) - (select SUM(cantidad)) FROM Gasto);

Check this: How to SUM and SUBTRACT using SQL?

edited May 23 '17 at 11:49

Community

1
1

answered Feb 16 '13 at 14:19

Arpit

12,767
3
27
40

Here you have got cross (Cartesian) join and as reason wrong result. – Hamlet Hakobyan Feb 16 '13 at 14:21

score 1 · Answer 4 · answered Feb 16 '13 at 14:22

1

If you aren't relating the 2 tables, and you just want to subtract the 2 sums, then this should work:

SELECT (SELECT SUM(ingresado) 
   FROM Nomina) - 
   (SELECT SUM(cantidad)
   FROM Gasto)

answered Feb 16 '13 at 14:22

sgeddes

62,311
6
61
83

score 1 · Answer 5 · answered Feb 16 '13 at 14:23

1

I think you want to do two separate queries and then do the substraction inside your code. Like:

SELECT SUM(ingresado) FROM Nomina;
SELECT SUM(cantidad) FROM Gasto;

Then you do the substraction in your code.

I'm sure it would also be possible to do the substraction directly inside the SQL, by using some nested queries, but thats only hassle without advantage.

answered Feb 16 '13 at 14:23

replay

3,569
3
21
30

Yes I knew that solution, but I needed to be in only one query because I'm using typed-dataset (I don't know if right typed) so I'm doing it through visual studio settings, in order to have less code and cleaner my application, and mvc way. – Alejandro L. Feb 16 '13 at 14:27

How to compose this SQL Server query?

5 Answers5