group-by

SQL "group by" question - I can't select every column

Hi, I have a database where each row has an id, a URL, and an XML. The IDs are unique, but URLs column can have duplicates. I need all the URLs, without duplicates, and for each URL I need the id and XML. If I ask only for the URL and the Id, I use the following query: select URL, max(ID) as ID from T1 group by URL And all is workin...

How can I read LINQ group by Query?

Hi there, this is my escenary, I Have a class called Plantilla that contains severals propertyes that allows bind a gridview in wpf, so that was working already, but users tells me that they need re-group the query by some field in database, so I prepare this snippet : var dsTemplates = (from t in db.PLANTILLAs ...

Using Linqdatasource and groupby property

I have a Linqdatasource that groups the records in table:Routing by a field called SubmitTo. My select statement is this - "new (key as SubmitTo, Count() as Count, it as Routings)". Now the SubmitTo field is only a foreign key reference the primary key in table:Department which has a field:DeptName with the full name of the department. H...

Complex join with nested group-by/having clause?

I ultimately need a list of "import" records that include "album" records which only have one "song" each. This is what I'm using now: select i.id, i.created_at from imports i where i.id in ( select a.import_id from albums a inner join songs s on a.id = s.album_id group by a.id having 1 = count(s.id) ); The nested sele...

How do I include empty rows in a single GROUP BY DAY(date_field) SQL query?

I'm using MS SQL Server but welcome comparitive solutions from other databases. This is the basic form of my query. It returns the number of calls per day from the 'incidentsm1' table: SELECT COUNT(*) AS "Calls", MAX(open_time), open_day FROM ( SELECT incident_id, opened_by, open_time - (9.0/24) AS open_time, DATEPART(dd...

SQL Group By question SQL Server 2005 CE

Hi everybody, I'm having a problem with an sql query that i use for my mobile application which uses sql server 2005 ce. I'm not so good with t-sql, so have a problem with this query SELECT TP.ID_TASK_MASTER, TP.ID_PROBLEM, TP.ID_TASK_PROBLE, P.DS_PROBLEM, TP.SW_HASOK, TP.SW_HASNOK, TP.SW_HASTOK, TP.SW_HASVALUE, TP.NO_...

Group by like row filtering

Lets say I have a table describing cars with make, model, year and some other columns. From that, I would like to get one full row of each make and model for latest year. Or to put it in another way. Each row would have unique combination of make and model with other data from corresponding row with largest value of year. Standard SQL...

Getting single records back from joined tables that may produce multiple records

I've got a student table and an enrollment table; a student could have multiple enrollment records that can be active or inactive. I want to get a select that has a single student record and an indicator as to whether that student has active enrollments. I thought about doing this in an inline UDF that uses the student ID in a join to ...

How can I group by specific timestamp intervals in C# using LINQ?

I have a list with tick objects including a pair of a double value and a timestamp. I want to separate the list into child-lists depending on a time interval (for example 15 minutes). One list only has the tick objects from: 8:00:00 - 8:14-59 usw usw C#-code: var result = from tick in listTicks group tick by tick.timestamp.Hour; Th...

Django 1.0/1.1 rewrite of self join

Is there a way to rewrite this query using the Django QuerySet object: SELECT b.created_on, SUM(a.vote) FROM votes a JOIN votes b ON a.created_on <= b.created_on WHERE a.object_id = 1 GROUP BY 1 Where votes is a table, object_id is an int that occurs multiple times (foreign key - although that doesn't matter here), and created_on whic...

LINQ: get access to child lists during runtime

i make a group by and get child lists... during query i create a new obj with var result3 = from tick in listTicks group tick by bla bla into g select new { Count = g.Count(), Key = g.Key, Items = g, Timestamp = g.First...

Group By and displaying entries under date

Here's my data: id date 1 2009-01-01 10:15:23 2 2009-01-01 13:21:29 3 2009-01-02 01:03:13 4 2009-01-03 12:20:19 5 2009-01-03 13:01:06 What I'd like to do is group by each date and then list the id numbers below each date. Should I be getting the distinct date values and then cycling through them to get the entries that fa...

T-SQL GROUP BY: Best way to include other grouped columns

I'm a MySQL user who is trying to port some things over to MS SQL Server. I'm joining a couple of tables, and aggregating some of the columns via GROUP BY. A simple example would be employees and projects: select empID, fname, lname, title, dept, count(projectID) from employees E left join projects P on E.empID = P.projLeader group by...

group-by question in postgresql

Say I have a table 'orders' created as: CREATE TABLE orders (id SERIAL, customerID INTEGER, timestamp BIGINT, PRIMARY KEY(id)); Timestamp being the UNIX timestamp. Now i want to select the ids of the LATEST orders for every customer. As a view would be nice. however the...

MySQL: multiple grouping

So I have an example table called items with the following columns: item_id (int) person_id (int) item_name (varchar) item_type (varchar) - examples: "news", "event", "document" item_date (datetime) ...and a table person with the following columns: "person_id", "person_name". I was hoping to display a list of the top 2 submitters (+...

Mysql Query not working

I have three tables that are structured like this: http://i41.tinypic.com/2bt9aq.png What I am trying to do is retrieve the joke id, title, and average rating of all jokes in a certain category and order them alphabetically. I have this query: $result = mysql_query(" SELECT jokedata.id AS joke_id, jokedata.joketitle AS joke_title, SUM(...

Inserting increment for each unique value in sql.

Hello, I've been tasked with a SQL problem that is outside of the limited scope of sql knowledge that I have. I have the following problem. I have a table that currently looks like this: widgets --------- a a a b b c d d d I would like to have another table that has each unique value incrementall...

Flattening Linq Group query

I have a list of data like so: ID AddressPurpose Address ... 1 L 1 P 2 L 2 P 3 P 3 L 4 P 4 L 5 P 6 L I want to be able to filter the data so that for each unique number if there is a P row then it is returned else the L row is returned. So the data will look like this: ID AddressPurpose Address ... 1 P 2 P 3 P 4 P 5 P ...

[LINQ] Operations on Lambda Grouping

Hello, Im stuck with a LINQ group by situation trying to solve it without using foreach statement, here is the escenary: I Have two generic collections List<OrderHeader> and List<OrderDetail>, both have a same field "TOTRGVS" that contains total amount from a order, and the number of order is the key named "NDORGV". Then I want to foun...

SQL group by "date" question

Hello. I'm trying to make a report but I'm having problems with my archi nemesis SQL. I have a table where the close date of a transaction is stored. I want to know how many transaction per month there was so I did: SELECT trunct( closedate, 'MONTH' ) FROM MY_TRANSACTIONS I'm using oracle. I'm getting a list like this: 2002-0...