Results 1 to 6 of 6
  1. #1
    Join Date
    Apr 2004
    Posts
    43

    Unanswered: Grouping, mins, and sums???

    I have a table that looks like this:
    artno | row | abt | pp
    ------+-----+-----+-----
    507 | 2 | C60 | 1.23
    507 | 4 | E45 | 0.59
    509 | 1 | F00 | 2.89
    509 | 2 | E45 | 0.00
    509 | 3 | C60 | 0.59
    510 | 2 | C60 | 1.55
    510 | 3 | F00 | 0.05


    I am trying to get the total production time (pp) for all article numbers (artno) where the department is C60, but only when the C60 department occupies the lowest row number for all records of that article number...

    That is:

    artno | row | abt | pp
    ------+-----+-----+-----
    507 | 2 | C60 | 1.23
    510 | 2 | C60 | 1.55


    And then the total pp for these results: 2.78.

    I hope it this makes some sense, and I would greatly appreciate any help i suggestions.

  2. #2
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    Perhaps something like this?
    Code:
    SELECT SUM(my_value) 
    FROM (SELECT SUM(pp) my_value 
    	  FROM mytab m
    	  WHERE m.abt = 'C60'
    	  GROUP BY m.artno, m.row_num
    	  HAVING m.row_num = (SELECT MIN(m1.row_num) 
    						  FROM mytab m1
    						  WHERE m1.artno = m.artno
    						 )
    	 );

  3. #3
    Join Date
    Apr 2004
    Posts
    43
    Thanks! But i get a parse error at or near "my_value", referring to the "...(SELECT SUM(pp) my_value...". My syntax is the same as yours...

    Any ideas where this comes from??

  4. #4
    Join Date
    Apr 2004
    Posts
    43
    Thanks! But i get a parse error at or near "my_value", referring to the "...(SELECT SUM(pp) my_value...". My syntax is the same as yours...

    Any ideas where this comes from??

  5. #5
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    OK, try this ...
    Code:
    SELECT SUM(m.pp) 
    FROM mytab m
    WHERE m.abt = 'C60'
    AND m.row_num = (SELECT MIN(m1.row_num)
    				 FROM mytab m1
    				 WHERE m1.artno = m.artno
    				 GROUP BY m1.artno
    				 );
    (both were written on Oracle 8.1.7; perhaps you SQL doesn't support the way first query was made).

  6. #6
    Join Date
    Apr 2004
    Posts
    43
    Thanks very much for your help. I am using a Postgres DB - but the new syntax did the trick!
    Cheers,
    Stephen.

Posting Permissions

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