Thread: How do I get the result of subtraction as opposed to the sum ()

1. Registered User
Join Date
Jan 2012
Posts
91

Unanswered: How do I get the result of subtraction as opposed to the sum ()

I use a query to sum records in the table:
Code:
`SELECT KOD,KRAJ,SUM(WARTOSC),SUM(WAGA),SUM(ILOSC) FROM TEMP GROUP BY KOD,KRAJ`
Is it possible to get the result of the subtraction of records in the 'group' query? Is subtraction always occurs between a higher value and lower and it may be the other way around? Thanks.
Last edited by duf; 03-13-13 at 06:26.

2. www.gvee.co.uk
Join Date
Jan 2007
Location
UK
Posts
11,445

At a very basic guess, have you tried something like this:
Code:
`SELECT KOD,KRAJ,SUM(WARTOSC) - SUM(WAGA),SUM(ILOSC) FROM TEMP GROUP BY KOD,KRAJ`

3. Registered User
Join Date
Jan 2012
Posts
91
Originally Posted by gvee

At a very basic guess, have you tried something like this:
Code:
`SELECT KOD,KRAJ,SUM(WARTOSC) - SUM(WAGA),SUM(ILOSC) FROM TEMP GROUP BY KOD,KRAJ`
Firstly, thank you for your reply. Let me give an example of the array.

KOD..KRAJ..WARTOSC..WAGA..ILOSC
100...PL.................1........3........4
100...PL.................2........1........1
123...PL.................1........1........1

After group with a 'group' I receive the sum of the first two records.

KOD..KRAJ..WARTOSC..WAGA..ILOSC
100...PL.................3........4........5
123...PL.................1........1........1

And I like to obtain their differences: 2-1 = 1, 3-1 = 2, 4-1 = 3

KOD..KRAJ..WARTOSC..WAGA..ILOSC
100...PL.................1........2........3
123...PL.................1........1........1

4. www.gvee.co.uk
Join Date
Jan 2007
Location
UK
Posts
11,445
And what happens if your data looks like this:

KOD..KRAJ..WARTOSC..WAGA..ILOSC
100...PL.................1........3........4
100...PL.................2........1........1
123...PL.................1........1........1
199...PL.................1........3........6
937...PL.................1........7........1

5. Registered User
Join Date
Jan 2012
Posts
91
Originally Posted by gvee
And what happens if your data looks like this:

KOD..KRAJ..WARTOSC..WAGA..ILOSC
100...PL.................1........3........4
100...PL.................2........1........1
123...PL.................1........1........1
199...PL.................1........3........6
937...PL.................1........7........1
The grouping criteria are clearly written:
Code:
`...GROUP BY KOD,KRAJ`
This part of the table is not grouped:
123...PL.................1........1........1
199...PL.................1........3........6
937...PL.................1........7........1

6. www.gvee.co.uk
Join Date
Jan 2007
Location
UK
Posts
11,445
I get the grouping part, don't worry

It's this following part that needs clarification!
Originally Posted by duf
And I like to obtain their differences: 2-1 = 1, 3-1 = 2, 4-1 = 3

KOD..KRAJ..WARTOSC..WAGA..ILOSC
100...PL.................1........2........3
123...PL.................1........1........1
What would your calculation look like in a resultset that has
4* rows (after grouping)..

* you missed KOD = 100

7. Registered User
Join Date
Feb 2008
Location
Japan
Posts
3,483
If there were 3 or more records in a 'group', what result do you want?

For example...

KOD..KRAJ..WARTOSC..WAGA..ILOSC
100...PL.................1........3........4
100...PL.................2........1........1
100...PL.................5........4........3
123...PL.................1........1........1

8. Registered User
Join Date
Jan 2012
Posts
91
Originally Posted by tonkuma
If there were 3 or more records in a 'group', what result do you want?

For example...

KOD..KRAJ..WARTOSC..WAGA..ILOSC
100...PL.................1........3........4
100...PL.................2........1........1
100...PL.................5........4........3
123...PL.................1........1........1
When grouping with the command sum ():
KOD..KRAJ..WARTOSC..WAGA..ILOSC
100...PL.................8........8........8
123...PL.................1........1........1
but otherwise I do not know

In that case, I must have two tables. Values ​​from one table must subtract the value from the second table.
Something like this:
Code:
```select A.WARTOSC - B.WARTOSC as ROZNICA
from
TABLEA A,
TABLEB B
where
B.KOD = A.KOD and B.KRAJ = A.KRAJ```

9. Registered User
Join Date
Feb 2008
Location
Japan
Posts
3,483
Originally Posted by duf
When grouping with the command sum ():
KOD..KRAJ..WARTOSC..WAGA..ILOSC
100...PL.................8........8........8
123...PL.................1........1........1
but otherwise I do not know

In that case, I must have two tables. Values ​​from one table must subtract the value from the second table.
Something like this:
Code:
```select A.WARTOSC - B.WARTOSC as ROZNICA
from
TABLEA A,
TABLEB B
where
B.KOD = A.KOD and B.KRAJ = A.KRAJ```
I didn't want to see the result of simple grouping(which you provided) nor result of other SELECT queries.

regardless how the result was generated by some queries or not.

For example:
" I must have two tables" is not mean any thing for me,
because, even if you might think so,
(but) there may be any other solutions(queries) which you didn't know.

The point might be what result do you want,
though you might not know how the result might be got(or generaed) by some queries.
Last edited by tonkuma; 03-13-13 at 15:14.

10. Registered User
Join Date
Jan 2012
Posts
91
Originally Posted by tonkuma
I didn't want to see the result of simple grouping(which you provided) nor result of other SELECT queries.

regardless how the result was generated by some queries or not.

For example:
" I must have two tables" is not mean any thing for me,
because, even if you might think so,
(but) there may be any other solutions(queries) which you didn't know.

The point might be what result do you want,
though you might not know how the result might be got(or generaed) by some queries.
Thanks for your patience. I'll try to explain again what I care about.

KOD..KRAJ..WARTOSC..WAGA..ILOSC
100...PL.................1........3........4
100...PL.................2........1........1
123...PL.................1........1........1

After group with a 'group' I receive the sum of the first two records.

KOD..KRAJ..WARTOSC..WAGA..ILOSC
100...PL.................3........4........5
123...PL.................1........1........1

and the solution was good. But now my tebala changed. Table reached records, which I must subtract. These records are marked (MARK=1) as well as records that are grouped with sum() (MARK=2).

KOD..MARK..KRAJ..WARTOSC..WAGA..ILOSC
100.....1......PL.................1........3...... ..4
100.....1......PL.................2........1...... ..1
123.....1......DE.................1........1...... ..1

100.....2......PL.................1........3...... ..4
100.....2......PL.................2........1...... ..1
123.....2......PL.................1........1...... ..1
123.....2......PL.................1........1...... ..1
458.....2......PL.................1........1...... ..1
458.....2......PL.................1........1...... ..1

I'll try to describe my problem again.
Records that have a column called 'Mark' with a value of 1, should be grouped and summarized. So can be used the 'group' with 'sum ()'.
Records that have a column called 'Mark' with a value of 2 also should be grouped and summarized using the 'group' and 'sum ()'. So we have two sets of results.

KOD..MARK..KRAJ..WARTOSC..WAGA..ILOSC
100.....1......PL.................3........4...... ..5
123.....1......DE.................1........1...... ..1

100.....2......PL.................3........4...... ..5
123.....2......PL.................2........2...... ..2
458.....2......PL.................2........2...... ..2
Now I need to subtract from the records of the 'MARK2 2' values ​​of records from the 'MARK 1' under the conditions where the 'KRAJ' and 'KOD' must be equal.
So in this example only one record meets the condition:
100.....2......PL.................3........4...... ..5 - (minus) 100.....1......PL.................3........4...... ..5

And the result should look like this:

100.....2......PL.................0........0...... ..0
123.....2......PL.................2........2...... ..2
458.....2......PL.................2........2...... ..2

And the table should no longer contain records from the 'MARK 1'. My idea is to create two tables. One would be the value of "MARK 1 'and the other values ​​of' MARK 2 '. I hope that is clearly expressed

Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•