Results 1 to 10 of 10
  1. #1
    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. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    More information please... Sample data and desired output would be niec.

    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
    George
    Home | Blog

  3. #3
    Join Date
    Jan 2012
    Posts
    91
    Quote Originally Posted by gvee View Post
    More information please... Sample data and desired output would be niec.

    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. #4
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    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
    George
    Home | Blog

  5. #5
    Join Date
    Jan 2012
    Posts
    91
    Quote Originally Posted by gvee View Post
    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. #6
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    I get the grouping part, don't worry

    It's this following part that needs clarification!
    Quote Originally Posted by duf View Post
    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
    George
    Home | Blog

  7. #7
    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. #8
    Join Date
    Jan 2012
    Posts
    91
    Quote Originally Posted by tonkuma View Post
    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. #9
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Quote Originally Posted by duf View Post
    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.

    Please publish your required result from my sample data,
    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. #10
    Join Date
    Jan 2012
    Posts
    91
    Quote Originally Posted by tonkuma View Post
    I didn't want to see the result of simple grouping(which you provided) nor result of other SELECT queries.

    Please publish your required result from my sample data,
    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.
    About this time I used the function 'group' to connect individual records:

    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
  •