How to Get List of Values in Group_By Clause

How to get array of values in GROUP BY clause using BigQuery?

try this

SELECT
  id,
  ARRAY_AGG(data)
FROM
  table_name
GROUP BY
  id

GROUP BY but get all values from other column

Using MySQL you can use GROUP_CONCAT(expr)

This function returns a string result with the concatenated non-NULL
values from a group. It returns NULL if there are no non-NULL values.
The full syntax is as follows:

GROUP_CONCAT([DISTINCT] expr [,expr ...]
             [ORDER BY {unsigned_integer | col_name | expr}
                 [ASC | DESC] [,col_name ...]]
             [SEPARATOR str_val])

Something like

SELECT ID, GROUP_CONCAT(name) GroupedName
FROM Table1
GROUP BY ID

SQL Fiddle DEMO

Get list of ids for each in group in group by statement sqlite

Without aggregation function you could get any value. SQLite is similiar to MySQL in that matter. In standard SQL you have to choose aggregation function, otherwise you will get error.

To get all values you could use GROUP_CONCAT:

SELECT field1, field2, GROUP_CONCAT(id, ',') AS ids
FROM my_table
GROUP BY field1, field2
HAVING COUNT(*) > 1;

SqlFiddleDemo

Output:

╔═════════╦═════════╦═══════╗
║ field1  ║ field2  ║  ids  ║
╠═════════╬═════════╬═══════╣
║      1  ║      2  ║ 1,5   ║
║      2  ║      1  ║ 2,3,6 ║
╚═════════╩═════════╩═══════╝

If you want ids only remove field1,field2 from select columns list.

SELECT GROUP_CONCAT(id, ',') AS ids
FROM my_table
GROUP BY field1, field2
HAVING COUNT(*) > 1

SqlFiddleDemo2

SQL Select a group when attributes match at least a list of values

In the where criteria filter on the list of values you would like to see, group by id and in the having clause filter on those ids which have 3 matching rows.

select id from table1
where value in ('A', 'B', 'C') --you can use a result of another query here
group by id
having count(*)=3

If you can have the same id - value pair more than once, then you need to slightly alter the having clause: having count(distinct value)=3

If you want to make it completely dynamic based on a subquery, then:

select id, min(valcount) as minvalcount from table1
cross join (select count(*) as valcount  from table1 where id=2) as t1
where value in (select value from table1 where id=2) --you can use a result of another query here
group by id
having count(*)=minvalcount

---

Related Topics