group-by

How do I use T-SQL Group By

I know I need to have (although I don't know why) an Order By clause on the end of a SQL query that uses any aggregate functions like count, sum, avg, etc: select count(userID), userName from users group by userName When else would GROUP BY be useful, and what are the performance ramifications?...

How to concatenate strings of a string field in a PostgreSQL 'group by' query?

I am going to answer my own question because I just found the answer, but thought it still worth posting here. I am looking for a way to concatenate the strings of a field within a group by query. So for example, I have a table: ID COMPANY_ID EMPLOYEE 1 1 Anna 2 1 Bill 3 2 Carol 4 2 ...

How do I return my records grouped by NULL and NOT NULL?

I have a table that has a processed_timestamp column -- if a record has been processed then that field contains the datetime it was processed, otherwise it is null. I want to write a query that returns two rows: NULL xx -- count of records with null timestamps NOT NULL yy -- count of records with non-null timestamps Is that...

"Most popular" GROUP BY in LINQ?

Assuming a table of tags like the stackoverflow question tags: TagID (bigint), QuestionID (bigint), Tag (varchar) What is the most efficient way to get the 25 most used tags using LINQ? In SQL, a simple GROUP BY will do: SELECT Tag, COUNT(Tag) FROM Tags GROUP BY Tag I've written some LINQ that works: var groups = from t in Dat...

How would I calculate timespans from a list of check in and check outs?

I'm writing a simple time tracking program to manage my own projects. I'm a big fan of keeping reporting code in the database, so I've been attempting to create a few sprocs that generate the invoices and timesheets etc. I have a table that contains Clock Actions, IE "Punch In", and "Punch Out". It also contains the user that did this a...

C# List<> GroupBy 2 Values

I'm using C# on Framework 3.5. I'm looking to quickly group a Generic List<> by two properties. For the sake of this example lets say I have a List of an Order type with properties of CustomerId, ProductId, and ProductCount. How would I get the sum of ProductCounts grouped by CustomerId and ProductId using a lambda expression? ...

SQL expression problem

Below is my table, a User could have multiple profiles in certain languages, non-English profiles have a higher priority. +----------+--------+----------------+----------------+ |ProfileID |UserID |ProfileLanguage |ProfilePriority | +----------+--------+----------------+----------------+ |1 |1 |en-US |2 ...

MySQL finding subtotals

EDIT: I'm told that making you guys read means I get less attention. My apologies. Here's a simpler version: Bill got $100 dollars worth of items from a store. He wants to return enough of the items to get exactly $30 dollars back. The store has a Point of Return system that will help him do this. Here is the data after he scans his...

SQL group by date, but get dates w/o records too

Is there an easy way to do a GROUP BY DATE(timestamp) that includes all days in a period of time, regardless of whether there are any records associated with that date? Basically, I need to generate a report like this: 24 Dec - 0 orders 23 Dec - 10 orders 22 Dec - 8 orders 21 Dec - 2 orders 20 Dec - 0 orders ...

Why does SQL force me to repeat all non-aggregated fields from my SELECT clause in my GROUP BY clause?

This has bugged me for a long time. 99% of the time, the GROUP BY clause is an exact copy of the SELECT clause, minus the aggregate functions (MAX, SUM, etc.). This breaks the Don't Repeat Yourself principle. Is there a good reason for this? edit I realise that some implementations allow you to have different fields in the GROU...

Grouping List by the last Char

Hi, As you can see on the picture above i have a List "keys" filled with Request.Form.AllKeys which a key starts with "txt". I want to group them by their last id, for example [0] "txtTitle:2" "txtDescription:2" [1] "txtTitle:3" "txtDescription:3" How can i achive this with lambda expression. Thank you very much ...

sql group by versus distinct

Why would someone use a group by versus distinct when there are no aggregations done in the query? Also, does someone know the group by versus distinct performance considerations in MySQL and SQL Server. I'm guessing that SQL Server has a better optimizer and they might be close to equivalent there, but in MySQL, I expect a significant...

MySQL Query - getting missing records when using group-by

I have a query : select score, count(1) as 'NumStudents' from testresults where testid = 'mytestid' group by score order by score where testresults table contains the performances of students in a test. A sample result looks like the following, assuming maximum marks of the test is 10. score, NumStudents 0 10 1 20 2 12 3...

MSSQL: Only last entry in GROUP BY

Hi everyone. I have the following table in MSSQL2005 id | business_key | result 1 | 1 | 0 2 | 1 | 1 3 | 2 | 1 4 | 3 | 1 5 | 4 | 1 6 | 4 | 0 And now i want to group based on the business_key returning the complete entry with the highest id. So my expected result is: business_key | result 1 | 1 2 | 1 3 | 1 4 | 0 I bet that there is ...

How to lambda the group by data on a LINQ to Sql results?

I get the data from the database like this. Dim query = From t1 In TBL1 _ Join t2 In TBL2 On t1.ID Equals t2.ID _ Join t3 In TBL3 On t1.ID Equals t3.ID _ Group Join t4 In t1 _ On t1.ID Equals t4.ID _ Into t4_Grp = Group _ Select t1, t2, t3, t4_Gr...

SQL select nth member of group

If I have a USER table like class | age -------------- 1 20 3 56 2 11 1 12 2 20 Then I can easily get the youngest user in each class via select class, min(age) from user group by class; Similarly, by replacing min with max, I can get the oldest. But how can I get the 10th youngest (or oldest) in...

How to get list of values in GROUP_BY clause?

If I have data like this in a table id data -- ---- 1 1 1 2 1 3 2 4 2 5 3 6 3 4 how do I get results like this in a query (on sybase server)? id data -- ---- 1 1, 2, 3 2 4, 5 3 6, 4 ...

Optmizing MySQL GROUP BY or DISTINCT on large views

Consider a view consisting of several tables... for example a v_active_car, which is made up of the tables car joined on to body, engine, wheels and stereo. It might look something like this: v_active_cars view SELECT * FROM car INNER JOIN body ON car.body = body.body_id INNER JOIN engine ON car.engine = engine.engine_id I...

MySQL - Control which row is returned by a group by.

I have a database table like this: id version_id field1 field2 1 1 texta text1 1 2 textb text2 2 1 textc text3 2 2 textd text4 2 3 texte text5 If you didn't work it out, it contains a number of versions of a row, and then ...

Table Group By - Tsql -

The first picture is my query. I need to obtain results in second picture. What to do? Which query do have to use? Select OrarioA, OrarioB, Max(IDOrario), (????)IDDettaglioOrarioA, (???)IDDettaglioOrarioB FROM TABLE GROUP BY OrarioA, OrarioB, ?????????????? I need respective IDDettaglioOrarioA, IDDettaglioOrarioB. How to complet...