SQL Aggregation function to get concrete value-CodePudding

I need help with an aggregation functionality.

what I want to know is if it is possible to extract a concrete value from a grouped query on one of the columns I return, like this

STORE

fruit	color	stock
apple	red	30
apple	green	5
banana	yellow	40
berry	red	5
pear	green	5

SELECT SUM(stock), [?] FROM store GROUP BY fruit

[?] -> i need to take a concrete value, for example RED. but the SUM must have 35 in apples.

can this be done without a subquery?

Thanks

I expect this results

Column A	Column B
35	red

in this case the query does not make sense but for my personal case it does. I try to use STRING_AGG to take the data and make explode in my code, but its not the best way i think

CodePudding user response：

I think you're looking for the GROUP BY clause. Try this:

SELECT SUM(stock), color
FROM store
GROUP BY color

This will return a list of all colors, and the sum of the stock for each color.

CodePudding user response：

I'm not entirely clear what you mean by a "concrete value" (singlular) as there are potentially two or more values... and you did mention STRING_AGG(). Also, you omitted the "fruit" from the query, which made things a bit confusing. Nonetheless, this will get either one Color value or all color values using STRING_AGG() OR MAX() and without a sub-query:

-- the WITH is just a way to get your data into the query
    ;
WITH AdriansData AS
     (SELECT * FROM (VALUES('apple', 'red', 30),
                           ('apple', 'green', 5),
                           ('banana', 'yellow', 40),
                           ('berry', 'red',   5),
                           ('pear', 'green',    5)
                                           ) AS X (fruit, color, stock)
         )

         SELECT fruit,
                SUM(stock),
                STRING_AGG(color, ', ') AS Colors,
                MAX(color) AS JustOneColor
                FROM AdriansData

                GROUP BY fruit