Grouping, mins, and sums???

steve_o · #1 (**permalink**) 12-08-04, 04:29

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.

Littlefoot · #2 (**permalink**) 12-08-04, 05:49

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
						 )
	 );

steve_o · #3 (**permalink**) 12-08-04, 07:45

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??

steve_o · #4 (**permalink**) 12-08-04, 08:06

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??

Littlefoot · #5 (**permalink**) 12-08-04, 09:35

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).

steve_o · #6 (**permalink**) 12-08-04, 10:27

Thanks very much for your help. I am using a Postgres DB - but the new syntax did the trick!
Cheers,
Stephen.

Internet Services

Web Development

Digital Marketing

Consumer Tech