Count of items in aggregated column in Athena

user_15479018 0
Viewed 40

I have some columns as listed below:

FirstName Fruits Date
Sean Doe Oranges 2022-06-03
Jon Doe Apples 2022-06-02
Jon Doe Mangoes 2022-06-02
Sean Doe Peaches 2022-06-02
Jon Doe Strawberries 2022-06-02
Sean Doe Blueberries 2022-06-02
Jon Doe Blueberries 2022-06-01

I'm looking for a query that would give me following output in Presto/ AWS Athena

FirstName aggregated_fruits Date count_of_fruits_on_one_date
Jon Doe Apples,Mangoes,Strawberries 2022-06-02 3
Jon Doe Blueberries 2022-06-01 1
Sean Doe Peaches,Blueberries 2022-06-02 2
Sean Doe Oranges 2022-06-03 1

My incorrect current query is returning this:

Select
    FirstName,
    Date,
    array_join(array_agg("Fruits"), ',') AS aggregated_fruits , 
    count(*) AS count_of_fruits_on_one_date 
FROM FruitTable
WHERE <condition> 
GROUP BY FirstName, Fruits, Date 
ORDER by count DESC;
FirstName aggregated_fruits Date count_of_fruits_on_one_date
Jon Doe Mangoes,Mangoes,Mangoes 2022-06-02 3
Jon Doe Apples, Apples 2022-06-02 2
Sean Doe Oranges 2022-06-03 1
athena presto amazon-athena sql
1 Answers

Your query contains one extra grouping - by Fruits. Remove it:

-- sample data
WITH dataset(FirstName, Fruits, Date) AS (
    VALUES ('Sean Doe'  , 'Oranges' ,'2022-06-03'),
        ('Jon Doe'  , 'Apples'  ,'2022-06-02'),
        ('Jon Doe'  , 'Mangoes' ,'2022-06-02'),
        ('Sean Doe' , 'Peaches' ,'2022-06-02'),
        ('Jon Doe'  , 'Strawberries'    ,'2022-06-02'),
        ('Sean Doe' , 'Blueberries' ,'2022-06-02'),
        ('Jon Doe'  , 'Blueberries' ,'2022-06-01')
)

-- query
Select
    FirstName,
    Date,
    array_join(array_agg(Fruits), ',') AS aggregated_fruits , 
    count(Fruits) AS count_of_fruits_on_one_date 
FROM dataset
GROUP BY FirstName, Date 
ORDER BY count(Fruits) desc

Output:

FirstName Date aggregated_fruits count_of_fruits_on_one_date
Jon Doe 2022-06-02 Apples,Mangoes,Strawberries 3
Sean Doe 2022-06-02 Peaches,Blueberries 2
Sean Doe 2022-06-03 Oranges 1
Jon Doe 2022-06-01 Blueberries 1
user_25012790
Related