Hello All, I have a database of format Product| Offc | Sale | Spares | Service | Rental -------+--------+------+--------+---------+--------- Prod_A | Offc_1 | 175 | 54 | 72 | 18 Prod_A | Offc_2 | 174 | 45 | 62 | 15 Prod_A | Offc_3 | 176 | 54 | 57 | 16 Prod_A | Offc_4 | 185 | 50 | 64 | 18 Prod_A | Offc_5 | 179 | 45 | 71 | 18 Prod_A | Offc_6 | 162 | 43 | 75 | 18 -------+--------+------+--------+---------+--------- Prod_B | Offc_1 | 178 | 57 | 72 | 18 Prod_B | Offc_2 | 198 | 48 | 55 | 18 Prod_B | Offc_3 | 199 | 41 | 52 | 16 Prod_B | Offc_4 | 159 | 48 | 54 | 19 Prod_B | Offc_5 | 153 | 50 | 65 | 15 Prod_B | Offc_6 | 157 | 40 | 51 | 19 -------+--------+------+--------+---------+--------- Prod_N | Offc_1 | 151 | 48 | 78 | 19 Prod_N | Offc_2 | 172 | 51 | 76 | 15 Prod_N | Offc_3 | 171 | 49 | 73 | 19 Prod_N | Offc_4 | 166 | 55 | 54 | 18 Prod_N | Offc_5 | 188 | 41 | 50 | 19 Prod_N | Offc_6 | 172 | 52 | 51 | 16 -------+--------+------+--------+---------+--------- I want to have the data output in below format, ie For each product, show the 'Sale' from each office + Max Sale + Office with Max Sale. Product|Offc1|Offc2|Offc3|Offc4|Offc5|Offc6|Max|Winner -------+-----+-----+-----+-----+-----+-----+---+------ Prod_A | 157| 160| 180| 155| 151| 186|186|Offc6 Prod_B | 198| 194| 151| 173| 150| 181|198|Offc1 Prod_C | 152| 150| 170| 166| 158| 168|170|Offc3 Prod_D | 151| 184| 196| 169| 187| 190|196|Offc3 Prod_E | 155| 185| 174| 157| 191| 151|191|Offc5 Prod_F | 189| 182| 162| 159| 199| 175|199|Offc5 Prod_G | 177| 156| 166| 163| 183| 154|183|Offc5 Prod_H | 159| 184| 166| 185| 156| 180|185|Offc4 -------+-----+-----+-----+-----+-----+-----+---+------ I tried below query to get partial results. SELECT Product, SUM( CASE WHEN Offc = 'Offc_1' THEN Sale ELSE NULL END ) AS 'Offc_1', SUM( CASE WHEN Offc = 'Offc_2' THEN Sale ELSE NULL END ) AS 'Offc_2', SUM( CASE WHEN Offc = 'Offc_3' THEN Sale ELSE NULL END ) AS 'Offc_3', SUM( CASE WHEN Offc = 'Offc_4' THEN Sale ELSE NULL END ) AS 'Offc_4', SUM( CASE WHEN Offc = 'Offc_5' THEN Sale ELSE NULL END ) AS 'Offc_5', SUM( CASE WHEN Offc = 'Offc_6' THEN Sale ELSE NULL END ) AS 'Offc_6', MAX(Sale) AS Highest, FROM book1 GROUP BY Product but I am not able to get the winner, this is something like HLookup in Excel However I was able to get the winner in another query SELECT Product,Offc, Sale FROM book1 WHERE (Product,Sale) IN ( SELECT Product,MAX(Sale) FROM book1 GROUP BY Product ) Now I am not able to join the two queries into one query and have the desired results BTW, We by having some other schema structure task can become easier, I can do that as I am still in process of designing the schema -- Yogesh
[Non-text portions of this message have been removed]
