group-by

Group By Dates using Linq

What I have is a set of users with join dates and I want to use GoogleChartSharp to create a chart showing how many users have joined each month. So I have this set of dates (US format) in a list: 01/01/2009 02/01/2009 02/12/2009 03/02/2009 03/12/2009 03/22/2009 Googlechartsharp requires me to do someth...

translate stored procedure - to Linq2SQL (count, max, group, orderby)

I've two tables (1:N) CREATE TABLE master (idMaster int identity (1,1) not null, TheName varchar( 100) null, constraint pk_master primary key(idMaster) clustered) and - CREATE TABLE lnk (idSlave int not null, idMaster int not null, constraint pk_lnk_master_slave(idSlave) primary key clustered) link between Master.idMaster and ...

What is the difference between group by, distinct, Union for selecting distinct values for multiple columns?

Hi gurus, This question explained about a way of getting distinct combination of multiple columns. But I want to know the difference between the methods of DISTINCT, UNION, GROUP BY keyword method for this purpose. I am getting different results when using them. My queries are like this Query 1. select column1, column2, column3 from ta...

MySQL SUM when using GROUP BY not working

Let's say we have this table: Symbol | Size A | 12 B | 5 A | 3 A | 6 B | 8 And we want a view like this: Symbol | Size A | 21 B | 13 So we use this: Select Symbol, sum(Size) from table group by Symbol order by Symbol ASC But instead we get this: Symbol | Size A | 12 B | 5 What am I...

How to exclude duplicate rows when joining a table with itself.

Here's a sample table to help illustrate my problem: mysql> select * from test; +----+--------------+--------+ | id | type | siteid | +----+--------------+--------+ | 1 | First Visit | 100 | | 2 | Second Visit | 100 | | 3 | First Visit | 300 | | 4 | First Visit | 400 | | 5 | Second Visit | 500 | | 6 | Sec...

SQL Group by & Max

Hello, I have a following data in a table: id name alarmId alarmUnit alarmLevel 1 test voltage psu warning 2 test voltage psu ceasing 3 test voltage psu warning 4 test temp rcc warning 5 test temp rcc ceasing I'd like to show only the most recent information about every colums group (alarmId,alarmUnit), so the result ...

How do I select multiple items from each group in a mysql query?

I have some forum data of the form post(author, thread_id, text) For each author, I would like to select 10 distinct thread_ids associated with that author (there may be more than 10, and the number will vary by author). I'm thinking of using GROUP BY to group on 'author', but I cannot understand how to express the LIMIT on each grou...

MySQL : selecting all corresponding fields using MAX and GROUP BY

I have this table : And I would like to make a request that would return for each deal_id the row with the highest timestamp, and the corresponding status_id. So for this example, I would have returned 2 rows : 1226, 3, 2009-08-18 12:10:25 1227, 2, 2009-08-17 14:31:25 I tried to do it with this query SELECT deal_id, status_id, ma...

Retrieving the last record in each group

There is a table "messages" that contains data as shown below: Id Name Other_Columns ------------------------- 1 A A_data_1 2 A A_data_2 3 A A_data_3 4 B B_data_1 5 B B_data_2 6 C C_data_1 If I run a query "select * from messages group by name", I will get the result as: 1 ...

Avoiding unnecessary sort in SQL Server GROUP BY?

I have tables of data samples, with a timestamp and some data. Each table has a clustered index on the timestamp, and then a data-specific key. Data samples are not necessarily equidistant. I need to downsample the data in a particular time range in order to draw graphs - say, going from 100,000 rows to N, where N is about 50. While I m...

Group by MySQL mess

I am really having a trouble figuring this one out. I have a table 'Comments': cmt_id (primary key, auto incr), thread_id, cmt_text And I have these records in it: cmt_id thread_id cmt_txt 5002 1251035762511 Alright, I second this. 5003 1251036148894 Yet another comment. 5001 1251035762511 I am starting a ...

Count of consecutive not null values

The count should be 3 and 1 in the following query. The count should be of the points earned consecutively. So once the user fails to earn any points, the count restarts. mysql> select name, count(*) from sortest group by name, (points = 0) OR (points is NULL) having name= 'john'; +------+----------+ | name | count(*) | +------+-------...

Distinct pair of values SQL

Consider create table pairs ( number a, number b ) Where the data is 1,1 1,1 1,1 2,4 2,4 3,2 3,2 5,1 Etc. What query gives me the distinct values the number column b has So I can see 1,1 5,1 2,4 3,2 only I've tried select distinct ( a ) , b from pairs group by b but gives me "not a group by expression" ...

Mysql Join query using a group by

I'm trying to write a query that will return ... Each distinct user id in the accesslog table. A count of how many distinct ip addresses each userid has accessed the site with, also from the accesslog table. That userid's name, email address, and company name, from the users table. I've got 1 and 2, but I'm not sure how to get 3. I s...

Cannot group by bit column

In sql server 2005, i in the query builder, i select "Add group by" to automatically add the group by clause to all of the fields i selected. If one or more of those fields are a bit type, i get an error. Why is this? Is casting the column to TINYINT a good fix? ...

ILookup<TKey, TVal> vs. IGrouping<TKey, TVal>

I've been having trouble articulating the differences between ILookup<TKey, TVal> and IGrouping<TKey, TVal>, and am curious if I understand it correctly now. LINQ compounded the issue by producing sequences of IGrouping items while also giving me a ToLookup extension method. So it felt like they were the same until I looked more closely...

Get maximum value of a GROUP BY query without subquery in mySQL

hi, i have some queries which group datasets and count them, e.g. SELECT COUNT(*) FROM `table` GROUP BY `column` now i have the number of rows for which column is the same, so far so good. problem is: how do i get the aggregate (min/max/avg/sum) values for those “grouped” counts. using a subquery sure is the easiest, but i was...

GROUP BY Optimization [MySQL]

Hey. I´ve got these two tables in a 1:n relation. CREATE TABLE IF NOT EXISTS `de_locations` ( `id` int(11) NOT NULL auto_increment, `user_id` int(11) default NULL, `author_id` int(11) NOT NULL, `city_id` int(11) NOT NULL, `district_id` int(11) NOT NULL, `title` varchar(150) collate utf8_unicode_ci NOT NULL, `description` tinytext collat...

I have to display a list of tracks GROUPED BY Album in WPF

Now I am aware that I probably need to use a ListBox and can use the GroupStyle stuff which completely 100% fits my needs. Only thing is that I've been told that: "Whenever a "GroupStyle" is set on the control, the panel that layouts the items changes from VirtualizingStackPanel to StackPanel (this is a hack in MS code)..." I will nee...

In SQL in a "group by" expression: how to get the string that occurs most often in a group?

Assume we have the following table: Id A B 1 10 ABC 2 10 ABC 3 10 FFF 4 20 HHH As result of a "group by A" expression I want to have the value of the B-Column that occurs most often: select A, mostoften(B) from table group by A; A mostoften(B) 10 ABC 20 HHH How do I achieve this in Oracle 10g? Rema...