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
aggregate-functions
Count cumulative total in Postgresql
With larger datasets, window functions are the most efficient way to perform these kinds of queries — the table will be scanned only once, instead of once for each date, like a self-join would do. It also looks a lot simpler. 🙂 PostgreSQL 8.4 and up have support for window functions. This is what it … Read more
PostgreSQL – GROUP BY clause
Postgres 9.1 or later, quoting the release notes of 9.1 … Allow non-GROUP BY columns in the query target list when the primary key is specified in the GROUP BY clause (Peter Eisentraut) The SQL standard allows this behavior, and because of the primary key, the result is unambiguous. Related: Return a grouped list with … Read more
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
Aggregate a single column in query with many columns
Simple query This can be much simpler with PostgreSQL 9.1 or later. As explained in this closely related answer: PGError: ERROR: aggregates not allowed in WHERE clause on a AR query of an object and its has_many objects It is enough to GROUP BY the primary key of a table. Since: foo1 is a primary … Read more
MySQL dynamic cross tab
The number and names of columns must be fixed at the time you prepare the query. That’s just the way SQL works. So you have two choices of how to solve this. Both choices involve writing application code: (1) Query the distinct values of way and then write code to use these to construct the … Read more
Grouped string aggregation / LISTAGG for SQL Server
http://www.simple-talk.com/sql/t-sql-programming/concatenating-row-values-in-transact-sql/ It is an interesting problem in Transact SQL, for which there are a number of solutions and considerable debate. How do you go about producing a summary result in which a distinguishing column from each row in each particular category is listed in a ‘aggregate’ column? A simple, and intuitive way of displaying data … Read more
LINQ aggregate and group by periods of time
You could round the time stamp to the next boundary (i.e. down to the closest 5 minute boundary in the past) and use that as your grouping: var groups = series.GroupBy(x => { var stamp = x.timestamp; stamp = stamp.AddMinutes(-(stamp.Minute % 5)); stamp = stamp.AddMilliseconds(-stamp.Millisecond – 1000 * stamp.Second); return stamp; }) .Select(g => new … Read more
SQL comma-separated row with Group By clause
You want to use FOR XML PATH construct: SELECT ACCOUNT, unit, SUM(state_fee), Stuff((SELECT ‘, ‘ + code FROM tblmta t2 WHERE t2.ACCOUNT = t1.ACCOUNT AND t2.unit = t1.unit AND t2.id = ‘123’ FOR XML PATH(”)), 1, 2, ”) [Codes] FROM tblmta t1 WHERE t1.id = ‘123’ GROUP BY ACCOUNT, unit See other examples here: SQL … Read more
Multiple array_agg() calls in a single query
DISTINCT is often applied to repair queries that are rotten from the inside, and that’s often expensive and / or incorrect. Don’t multiply rows to begin with, then you don’t have to fold unwanted duplicates at the end. Joining to multiple n-tables (“has many”) multiplies rows in the result set. That’s efectively a CROSS JOIN … Read more