group-by

MySQL GROUP BY, and testing the grouped items

I've got a query like this: select a, b, c, group_concat(d separator ', ') from t group by a; This seems to work just fine. As I understand it (forgive me, I'm a MySQL rookie!), it's returning rows of: each unique a value for each a value, one b and c value also for each a value, all the d values, concatenated into one string Thi...

How to order or choose rows in MySQL GROUP BY clause?

I have a table like this: id number otherfields ------------------------------------------- 664 48 aaa 665 49 bbb 666 55 ccc 667 48 ddd My query groups by the number field, and I want it to pick the first (lowest) id, so that the data comes out like ccc,aaa,bbb (when ordered by numb...

Simple sql to Linq query with group by and aggregate functions

I'm fighting with linq trying to learn the syntax and I can't figure out how to do the following simple query SELECT DISTINCT user.firstname, user.lastname, COUNT(invoice.amount), SUM(invoice.amount) FROM company_user INNER JOIN user ON company_user.user_id = user.user_id INNER JOIN ...

SQL distinct or group by

My brain aint functioning atm.. Can you pls help me out here: Got a table with date, analyseid, groupid I want to get unique groupid's.. perhaps with a count on same groupid's. Also if there are rows with same groupid the date is also the same. SELECT date, analyseid, COUNT(*) AS 'amount', groupid FROM myTable GROUP BY gr...

Ruby/Rails - Group By performance

I have a collection of Episodes which is connected to a Season and a Show. I need to display them as such: Show title ....Season number 1 ........Episode name ........Episode name ....Season number 2 ........Episode name ........Episode name My controller: def index @show_ids = Following.find_all_by_user_id(curre...

LINQ GroupBy month

I am having trouble getting an IQueryable list of a (subsonic) object grouped by Month and Year. Basic view of the object... public partial class DatabaseObject { [SubSonicPrimaryKey] public int objectID { get; set; } public string Description { get; set; } public decimal Value { get; set; } public string Categor...

Object Must Implement IConvertible LINQ to Entities query containing grouping

Hi. I have an ASP.NET MVC application in which i am using entity model for connecting to MySQl database Here I am joining 2 tables and then applying group by on one of the fields. Then fetching the result from GroupBy result. Everything is working fine on development machine but getting next error: Object Must Implement IConvertible...

scala list of objects, using groupBy with average

Hi, basically I'm not really a Java/Scala fan, but unfortunately I'm forced to use it for my studies. Anyways, I was given an assignment: What the program gets is a list of objects like: Mark(val name String, val style_mark Int, val other_mark Int). How can I use groupBy, to group the marks by name, and get an average for style_mark an...

Linq to SQL - Group By Table

This doesn't work: Dim Qry = From RE In DB.Res_Estab Where _ (RE.Estab_Code = Estab_Code) Group By RE.Research Into Group Select Research Because Research is a table, not a value. It renders the error "A group by expression can only contain non-constant scalars that are comparable by the server." So I'm splitting in two queries: Dim...

How can I make this MySQL SELECT + GROUP BY query more efficient?

I have a fairly popular site that's now getting rammed with a lot of traffic, and I've been informed by my webhost that the following query is taking up to 2 seconds to run. My MySQL skills aren't that great, so I'm sure I'm doing something wrong, but I'm not sure what could be done to improve it. For simplicity's sake, assume live_blu...

getting problem in group by clause

hi friends, i want to get the sum of quantity from the below query. how i write the group by clause in below mention query. select top 1 EvrId, TimeStamp, Date, BnhTnkMik1 as Quantity, (select TnkCode from Tanklar where TnkId=BnhTnkId) as Tank, FuelCode, InvoiceNo from Evrak join Hareket on (BnhEvrId=EvrId) join Stoklar on (StokId=Bn...

SQL Server Max statement returns multiple results

When querying two tables (t1, t2) and using a MAX statement for a column in t2, SQL returns multiple entries. This seems to be because I also query other info from t2 which consists of unique entries. Simplified table example t1.number t2.number_id t2.sync_id t2.text 1 1 1 ...

Using SQL GROUP BY to see when a field changes

I have a sql query that groups by ID and TYPE that returns the following result set ID DATE TYPE 101667712, 2008-10-31, W9 101667712, 2009-01-05, W9 101667712, 2009-01-05, W9 101667712, 2009-01-05, W9 101667712, 2009-01-05, W9 101667712, 2009-01-05, W9 101667712, 2009-01-05, W9 101667712, 2009-01-05, W8 101667712, 2009-01...

Rails : How to build statistics per day/month/year or How database agnostic SQL functions are missing (ex. : STRFTIME, DATE_FORMAT, DATE_TRUNC)

Hi everyone, I have been searching all over the web and I have no clue. Suppose you have to build a dashboard in the admin area of your Rails app and you want to have the number of subscriptions per day. Suppose that you are using SQLite3 for development, MySQL for production (pretty standard setup) Basically, there are two options ...

Looking to group specific items from a list based on names using linq, html helper related

Hi, I have a list of items I am retrieving which i wish to be grouped into divs depending on the common name that a set of the list items may have say for instance a list of firstnames I have. i would like to be able to create a div dynamically based on the items common attibutes. id 23 fistname darren id 37 fistname darren id 67 ...

Linq group by question

Hello, I have a datasource with columns id,myname,eventime,ceasetime. I need to find out duplicate date ranges for a given Key. I tried to use - (from data1 in myDatasource from data2 in myDatasource where data1.SeqId != data2.SeqId && data1.myname ==data2.myname where data1.Event_Time <=data2.Cease_...

how does the groupby and count work in sql

1> select browser,count(*) from logtest group by browser; +-----------+----------+ | browser | count(*) | +-----------+----------+ | Firefox 3 | 14 | | Unknown | 11 | +-----------+----------+ 2 rows in set 2> select browser,count(browser) from logtest group by browser; +-----------+----------------+ | browser | cou...

selecting a distinct count from LINQ Stored Proc

Hi there I have a stored proc that returns a resultset thus: testID(guid), testName, outcomeID(guid), outcomeName - fields testGuid1, testName1, OutcomeGuid1, outcome1 testGuid1, testName1, OutcomeGuid2, outcome2 testGuid1, testName1, OutcomeGuid3, outcome3 testGuid1, testName1, OutcomeGuid4, outcome4 testGuid1, testName1, Outcome...

select the sum of every client's last order

Hello, My data is as follows: ORDER_ID CLIENT_ID DATE VALUE 1881 51 2010-07-19 100.17 1882 50 2010-07-19 100.17 2754 50 2010-07-25 135.27 2756 50 2010-07-25 100.28 5514 50 2010-07-27 121.76 5515 50 2010-07-28 109.59 5516 50 2010-07-27 135...

SQLite -- sum over small sections of column based on index in other column

I would like to sum over the product of two columns, but I would like to do it in small sections. That is, for every symbol for every date I would like to sum the product of oi and 'contract_settle`. Here's a snippet of my SQLite db: date symbol cont oi contract_settle 20030103 NVLS1C 100 NA 20030103 NVLS1C 03F 19 3...