I have many shopper records who have a unique ID, there will be instances where a shopper with the same ID exists and I would like to sum up their shoppable_rated score and only return 1 result of the total sum of shoppable_rated for those IDs, so there will be no duplicate rows for the ID and their total score will be added up. Please can you advise how I would change my query? I got a little stuck trying to use HAVING and couldn't figure it out.
Many thanks!
SELECT
id,
shoppable,
day,
case when shoppable in ('Good','Very Good','Amazing') then 1 else 0 end as shoppable_rated
FROM
shoppers
WHERE
shoppable != 'Unknown'
GROUP BY
id
ORDER BY
shoppable_rated DESC;
Example of my data:
id, shoppable, day, shoppable_rated
1, Good, 26, 1
2, Very Good, 14, 1
3, Not Great, 3, 0
1, Bad, 16, 0
3, Amazing, 30, 1
First, remove all the unaggregated columns that are not needed in the result set.
Second, use an aggregation function:
SELECT id,
SUM(case when shoppable in ('Good','Very Good','Amazing') then 1 else 0 end) as shoppable_rated
FROM shoppers
WHERE shoppable <> 'Unknown'
GROUP BY id
ORDER BY shoppable_rated DESC;
If you need the data by id and day, then include both columns in both the SELECT and the GROUP BY:
SELECT id, day,
SUM(case when shoppable in ('Good','Very Good','Amazing') then 1 else 0 end) as shoppable_rated
FROM shoppers
WHERE shoppable <> 'Unknown'
GROUP BY id, day
ORDER BY shoppable_rated DESC;
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments