SQL: How to query a group of like records with in a query

March 1, 2019 6:27 AM

I have an SQL view that returns multiple records with the same ID. I can query the view but do not have access to change the view. I need to apply some logic to each set of records with the same ID to create a single record per ID. Example inside:

Example Data
ID | Color
100 |red
100 |green
100 |blue
100 |purple
200 |red
200 |red
200 |green
200 |yellow

Logic: If any color = yellow then color =slow else color = not slow

return
ID | Color
100| not slow
200| slow

Is this possible using SQL only?

posted by jmsta to Technology (6 answers total)

This is working in Microsoft Access :
SELECT Table2.ID, "Slow" AS Expr1
FROM Table2 WHERE (((Table2.TXT)="Yellow"));
UNION
SELECT DISTINCT Table2.ID, "Not Slow" AS Expr1
FROM Table2 WHERE (((Table2.ID) Not In (SELECT Table2.ID
FROM Table2 WHERE (((Table2.TXT)="Yellow")))));
posted by elgilito at 6:46 AM on March 1, 2019

SELECT ID, CASE WHEN yellow_ct>0 THEN 'slow' ELSE 'not slow' END AS color2
FROM (
SELECT ID, COUNT(CASE WHEN COLOR='YELLOW' THEN 1 ELSE 0) END AS yellow_ct
FROM table_name
GROUP BY ID)
GROUP BY ID

I think you need SUM, not COUNT, in the subquery - COUNT is going to return the number of rows, whether or not yellow_ct is 1 or 0

SELECT Table2.ID, "Slow" AS Expr1
FROM Table2 WHERE (((Table2.TXT)="Yellow"));
UNION
SELECT DISTINCT Table2.ID, "Not Slow" AS Expr1
FROM Table2 WHERE (((Table2.ID) Not In (SELECT Table2.ID
FROM Table2 WHERE (((Table2.TXT)="Yellow")))));

If Color can be Null, then the Not In predicate will not return the ID of a row with a Null color, iirc.
posted by thelonius at 6:53 AM on March 1, 2019

If Color can be Null, then the Not In predicate will not return the ID of a row with a Null color, iirc.
Just tested it with the built-in Access DB engine and it works. But yes, this should be tested with jmsta's database.
posted by elgilito at 7:57 AM on March 1, 2019

select s1.id, 
  case when exists(select 1 from scratch s2 where s1.id = s2.id and color = 'yellow') then 'slow'
  else 'not slow'
  end as speed
from scratch s1
group by s1.id;

posted by 4rtemis at 10:38 AM on March 1, 2019

This works in postgres, using 'speed' as the slow/not slow column:

SELECT
DISTINCT id,
CASE WHEN (COUNT(color) FILTER (WHERE color = 'yellow')) > 0 THEN 'slow' ELSE 'not slow' END AS speed
FROM
colors
GROUP BY
id
ORDER BY
id;
posted by alphanerd at 10:44 AM on March 1, 2019

If Color can be Null, then the Not In predicate will not return the ID of a row with a Null color, iirc.
Just tested it with the built-in Access DB engine and it works.

Really - for an ID with ONLY rows with null in the color field?
posted by thelonius at 10:55 AM on March 1, 2019