MySQL Query GROUP BY day / month / year
GROUP BY YEAR(record_date), MONTH(record_date) Check out the date and time functions in MySQL.
group-by
Most efficient method to groupby on an array of objects
If you want to avoid external libraries, you can concisely implement a vanilla version of groupBy() like so: var groupBy = function(xs, key) { return xs.reduce(function(rv, x) { (rv[x[key]] = rv[x[key]] || []).push(x); return rv; }, {}); }; console.log(groupBy([‘one’, ‘two’, ‘three’], ‘length’)); // => {3: [“one”, “two”], 5: [“three”]}
Group By Multiple Columns
Use an anonymous type. Eg group x by new { x.Column1, x.Column2 }
Group by in LINQ
Absolutely – you basically want: var results = from p in persons group p.car by p.PersonId into g select new { PersonId = g.Key, Cars = g.ToList() }; Or as a non-query expression: var results = persons.GroupBy( p => p.PersonId, p => p.car, (key, g) => new { PersonId = key, Cars = g.ToList() }); … Read more
Retrieving the last record in each group – MySQL
MySQL 8.0 now supports windowing functions, like almost all popular SQL implementations. With this standard syntax, we can write greatest-n-per-group queries: WITH ranked_messages AS ( SELECT m.*, ROW_NUMBER() OVER (PARTITION BY name ORDER BY id DESC) AS rn FROM messages AS m ) SELECT * FROM ranked_messages WHERE rn = 1; This and other approaches … Read more
Using group by on multiple columns
Group By X means put all those with the same value for X in the one group. Group By X, Y means put all those with the same values for both X and Y in the one group. To illustrate using an example, let’s say we have the following table, to do with who is … Read more
Select first row in each GROUP BY group?
DISTINCT ON is typically simplest and fastest for this in PostgreSQL. (For performance optimization for certain workloads see below.) SELECT DISTINCT ON (customer) id, customer, total FROM purchases ORDER BY customer, total DESC, id; Or shorter (if not as clear) with ordinal numbers of output columns: SELECT DISTINCT ON (2) id, customer, total FROM purchases … Read more