How to do a count on a union query
If you want a total count for all records, then you would do this: SELECT COUNT(*) FROM ( select distinct profile_id from userprofile_… union all select distinct profile_id from productions_… ) x
aggregate-functions
How to group mysql rows with same column value into one row?
Use GROUP_CONCAT() like this: SELECT k.id, GROUP_CONCAT(d.value) FROM keywords AS k INNER JOIN data as d ON k.id = d.id GROUP BY k.id Also, you may need to do ORDER BY d.name to get exact order of values as you want. Like this: SELECT k.id, GROUP_CONCAT(d.value ORDER BY d.name separator ‘ ‘) FROM keywords AS … Read more
How to use Oracle’s LISTAGG function with a unique filter? [duplicate]
I don’t have an 11g instance available today but could you not use: SELECT group_id, LISTAGG(name, ‘,’) WITHIN GROUP (ORDER BY name) AS names FROM ( SELECT UNIQUE group_id, name FROM demotable ) GROUP BY group_id
How to select data where a field has a min value in MySQL?
this will give you result that has the minimum price on all records. SELECT * FROM pieces WHERE price = ( SELECT MIN(price) FROM pieces ) SQLFiddle Demo
mysql count group by having
One way would be to use a nested query: SELECT count(*) FROM ( SELECT COUNT(Genre) AS count FROM movies GROUP BY ID HAVING (count = 4) ) AS x The inner query gets all the movies that have exactly 4 genres, then outer query counts how many rows the inner query returned.
MySQL: GROUP_CONCAT with LEFT JOIN
Use: SELECT t.*, x.combinedsolutions FROM TICKETS t LEFT JOIN (SELECT s.ticket_id, GROUP_CONCAT(s.soution) AS combinedsolutions FROM SOLUTIONS s GROUP BY s.ticket_id) x ON x.ticket_id = t.ticket_id Alternate: SELECT t.*, (SELECT GROUP_CONCAT(s.soution) FROM SOLUTIONS s WHERE s.ticket_id = t.ticket_id) AS combinedsolutions FROM TICKETS t
use mysql SUM() in a WHERE clause
You can only use aggregates for comparison in the HAVING clause: GROUP BY … HAVING SUM(cash) > 500 The HAVING clause requires you to define a GROUP BY clause. To get the first row where the sum of all the previous cash is greater than a certain value, use: SELECT y.id, y.cash FROM (SELECT t.id, … Read more
GROUP BY and COUNT in PostgreSQL
I think you just need COUNT(DISTINCT post_id) FROM votes. See “4.2.7. Aggregate Expressions” section in http://www.postgresql.org/docs/current/static/sql-expressions.html. EDIT: Corrected my careless mistake per Erwin’s comment.
Selecting a Record With MAX Value
Note: An incorrect revision of this answer was edited out. Please review all answers. A subselect in the WHERE clause to retrieve the greatest BALANCE aggregated over all rows. If multiple ID values share that balance value, all would be returned. SELECT ID, BALANCE FROM CUSTOMERS WHERE BALANCE = (SELECT MAX(BALANCE) FROM CUSTOMERS)
Difference between scalar, table-valued, and aggregate functions in SQL server?
Scalar Functions Scalar functions (sometimes referred to as User-Defined Functions / UDFs) return a single value as a return value, not as a result set, and can be used in most places within a query or SET statement, except for the FROM clause (and maybe other places?). Also, scalar functions can be called via EXEC, … Read more