Мой набор данных:
item_category,year,price
"Bacon",1910,0.255
"Bacon",1911,0.247
"Bacon",1912,0.244
"Bacon",1913,0.27
"Bread",1913,0.056
"Bread",1914,0.063
"Bread",1915,0.07
"Bread",1916,0.073
Мой код до сих пор:
SELECT
string_agg(DISTINCT item_category, ' and ' ORDER BY item_category) AS items,
year,
sum(avg_yr_value) AS price
FROM
earnings_and_prices
WHERE
(item_category = 'Bacon' OR item_category = 'Bread')
GROUP BY
year
ORDER BY
year
Мой вывод (я использую продолжение драгоценного камня Ruby, так что это в формате Hash):
{:items=>"Bacon", :year=>1910, :price=>0.255}
{:items=>"Bacon", :year=>1911, :price=>0.247}
{:items=>"Bacon", :year=>1912, :price=>0.244}
{:items=>"Bacon and Bread", :year=>1913, :price=>0.326}
{:items=>"Bacon and Bread", :year=>1914, :price=>0.338}
{:items=>"Bacon and Bread", :year=>1915, :price=>0.339}
{:items=>"Bacon and Bread", :year=>1916, :price=>1.017}
Мне нужны только те годы, которые содержат цены и на бекон, и на хлеб. Как мне это сделать?