MySQL Group By with top N number of each kind
I have a table like this: Rank Letter 1 A 2 A 3 B 4 A 5 C 6 A 7 C 8 C 9 B 10 C And I need the top 2 of each letter ordered by ascending rank: Rank Letter 1 A 2 A 3 B 5 C 7 C 9 B How would I do ...
group-by
How can I use Group By (instead of self-joins) to get correct data (specific example included)
Looking at the related questions, I don't think this specific question has been asked, so here goes. I had a situation where I joined on a table three times to get different data based on dates. This took too long, so in an effort to optimize, I rewrote it using a group by as defined here: http://weblogs.sqlteam.com/jeffs/jeffs/archive...
Select, group and sum results from database
Hello I have a database with some fields I'd like to sum. But that's not the big problem, I want to group those fields by the month they were created. ActiveRecord automaticaly created a field named "created_at". So my question; how can I group the result by month, then sum the fields for each month? Updated with code @hours = Hour.al...
MySql: Grouping a grouped statement possible?
Hi, I have a SQL Statement with some joins and grouping applied. I also have a Count() column. This is what the resulting data looks like: +----------------+-----------------+----------------+ | EMPLOYEEID | REQTYPE | SHORT (count) | +----------------+-----------------+----------------+ | 1 | 5 ...
MS Access Rounding Precision With Group By
Why doesn't the average of the score of an employee of each month, when summed, equal the average of the employees score (ever)? Average SELECT Avg(r.score) AS rawScore FROM (ET INNER JOIN Employee AS e ON ET.employeeId = e.id) INNER JOIN (Employee AS a INNER JOIN Review AS r ON a.id = r.employeeId) ON ET.id = r.ETId WHERE (((e.id)=@em...
Table of averages by date for multiple IDs
I have a table full of items that each have a unique ItemID. There is another table with information on tests that are done on these items around once a month (some may have multiple tests per month and some may have none some months) and each test entry has a full date mm/dd/yyyy. I would like to display the average of one of the fiel...
Getting a percentage from MySql with a group by condition, and precision
I was about to ask the MySql list this and remembered about SO. Running MySql 5.0.85, I need to be as efficient as possible about a few queries. If I could get a little review, I would appreciate it. I collect data in the millions, and need the top 50 grouped by one field, with a percentage of how much those top 50 occupy. Here is wh...
how to group by and count using MySQL
I have data which looks like this: ID post_author post_title guid 3309 21 Should somebody not yet on SQL 2008 wait for SQL 2008 R2, since it's near release? http://sql.stackexchange.com/questions/379/should-somebody-not-yet-on-sql-2008-wait-for-sql-2008-r2-since-its-near-release 1695 429 How do we politely decline well meaning a...
MYSQL SUM GROUP BY
I'm working on a high school grading system. At my school, grades can be changed by reworking problems and I store these changes with dates. I have a function that properly returns averages because the most recent grade is flagged with a "current" field with a value of '1'. I'd like to make the function capable of returning the most re...
Can I select distinct/group by characters in a field in SQL?
I'd like to run a query which for a given field will count the instances of a particular character. For example if I had a table called 'Friends' with a 'Names' field containing rows: Edward, James, Mike. I'd get the output: A 2, D 2, E 3, I 1, and so on... ...
how to use group by with union in t-sql
hi all, how can i using group by with union in t-sql? i want to group by the first column of a result of union, i wrote the following sql but it doesn't work. i just don't know how to reference the specified column (in this case is 1) of the union result. great thanks. SELECT * FROM ( SELECT a.id , a.time ...
Counting Instances of Unique Value in Field
Suppose you have a table in SQL: Prices ------ 13.99 14.00 52.00 52.00 52.00 13.99 How would you count the amount of times a DIFFERENT field has been entered in? Therefore an example of such a count would output: 13.99 - 2 times. 14.00 - 1 times. 52.00 - 3 times. OR perhaps: 3 (i.e. 13.99, 14.00, 52.00) Can anyone advise? ...
Need Linq translation for the following SQL Query
select colId, colTaskType, MaxID from tblTaskType join ( select tblCheckList.colTaskTypeID, max(colItemNumber) MaxID from tblCheckList group by colTaskTypeID ) x on coltaskTypeID = tblTaskType.colID ...
MySQL group by intervals in a date range
I am going to be graphing netflow data stored in a MySQL database, and I need an efficient way to get the relevant data points. They records are stored with the date as an int for seconds since epoch. I Would like to be able to something like: Select SUM(bytes) from table where stime > x and stime < Y group by (10 second intervals) Is ...
How can I do a contiguous group by in MySQL?
How can I return what would effectively be a "contiguous" GROUP BY in MySQL. In other words a GROUP BY that respects the order of the recordset? For example, SELECT MIN(col1), col2, COUNT(*) FROM table GROUP BY col2 ORDER BY col1 from the following table where col1 is a unique ordered index: 1 a 2 a 3 b 4 b 5 a 6 a ...
Linq2sql groupby concat
I have an expression that returns the following data ID Name Date 1 BRN 01/12/2009 00:00:00 1 BRN 01/06/2009 00:00:00 2 WTI 01/12/2009 00:00:00 2 WTI 01/06/2009 00:00:00 I'd like to add a group by ID and Name and then concat the two dates together with a hyphen seperating. My only solution so far is to get all the data th...
Mysql GROUP BY and COUNT for multiple WHERE clauses
Simplified Table structure: CREATE TABLE IF NOT EXISTS `hpa` ( `id` bigint(15) NOT NULL auto_increment, `core` varchar(50) NOT NULL, `hostname` varchar(50) NOT NULL, `status` varchar(255) NOT NULL, `entered_date` int(11) NOT NULL, `active_date` int(11) NOT NULL, PRIMARY KEY (`id`), KEY `hostname` (`hostname`), KEY `st...
Select and group rows in summary by hour
I have a table containing view/click records. The time is stored in a unix timestamp and I need to be able to pull out all of them within the specific month/day (based off of timestamps), but more importantly and the part I don't know how to do is group them by hour. I need to be able to do this in a single query rather than looping thr...
Linq continuous date takewhile
Main problem: how do i group elements by their Date, only if continuous and only if they match some properties? Details: Given this type of object: public class MyObj { public DateTime Date { get; set; } public string ConditionalValue1 { get; set; } public int ConditionalValue2 { get; set; } public int OtherValue { g...
LINQ and Grouping from a simple SQL DB relationship
I have two tables with the following layout and relationships: Tasks: TaskID StartTime EndTime TaskTypeID ProductionID ------------------------------------------------------------ 1 12:30 14:30 1 1 2 14:30 15:30 2 1 3 11:10 13:40 2 ...