group-by

Multiple Counts in SQL Query

Give the following simple table structure: Departments PK - DeptID DeptName -------------------------- 1 Department 1 2 Department 2 3 Department 3 4 Department 4 Groups PK - GroupdID DeptID -------------------------- 1 1 2 1 3 3 4 ...

Displaying rows with count 0 with mysql group by

I have two tables in MySql Company : (cname,city) works : (ename,cname,salary) I want to display number of employees working for every company, even if that number is zero. for e.g. for Company : Microsoft Bangalore IBM NY works : emp1 Microsoft 10000 emp2 Microsoft 90000 output should be : Microsoft 2 IBM 0 But the fo...

Getting non-existant month/week/year when using MySql group by clause with month/week/year date functions.

I am trying to implement a query where I am using aggregates to sum certain values and then group by mysql date functions (month | year | week). Those group by clauses don't return non-existant months OR year OR week respectively for obvious reasons. I was wondering if there is a way to get them? ...

sql query - how to apply limit within group by

hey guys assuming i have a table named t1 with following fields: ROWID, CID, PID, Score, SortKey it has the following data: 1, C1, P1, 10, 1 2, C1, P2, 20, 2 3, C1, P3, 30, 3 4, C2, P4, 20, 3 5, C2, P5, 30, 2 6, C3, P6, 10, 1 7, C3, P7, 20, 2 what query do i write so that it applies group by on CID, but instead of returning me 1 si...

How do I order a Group result, in Linq?

Hi folks, I have the following linq query, which works fine. I'm not sure how i order the group'd result. from a in Audits join u in Users on a.UserId equals u.UserId group a by a.UserId into g select new { UserId = g.Key, Score = g.Sum(x => x.Score) } the results are currently ordered by UserId ascending. I'm after Score descending. ...

TOP N problem with GROUP BY clause

The problem: I need to find all active [GiftPledges] that have the last three [GiftDetails] have a zero amount. SELECT gp.PledgeId FROM GiftPledge gp INNER JOIN GiftDetail gd ON gp.PledgeId = gd.PledgeId WHERE gp.PledgeStatus = 'A' GROUP BY PledgeId HAVING COUNT(PledgeId) >= 3 Now, I have all my [GiftPledges] t...

LINQ: Group by month and year within a datetime field

I have a table with a datetime field. I want to retrieve a result set grouped by the month/year combination and the number of records that appear within that month/year. How can this be done in LINQ? The closest I've been able to figure out is in TSQL: select substring(mo,charindex(mo,'/'),50) from ( select mo=convert(varchar(2),mont...

Group Union

I have the following query that always give me two rows. I want to group them to one row using tbluser.userid, is it possible to group a union? SELECT SUM(tblfooditem.calories) FROM tblfooditem INNER JOIN tbluser ON tblfooditem.userid = tbluser.userid WHERE tblfooditem.userid=?userid AND tblfooditem.date=?date GROUP BY tbluser.user...

PLSQL : Get sum for each day of week and total sum for week in a single query.

Let's say , I have a table, ClientTrade, like thus : ClientName , TradeDate , Quantity And I want to create a query in Oracle PLSQL which should return the result like this : (The days are derived from the TradeDate column and Mon = sum(Quantity) for Mon , Tue = sum(Quantity) for Tue ... etc.) ClientName Mon Tue Wed Thu Fri Sat Sun ...

Rails: Generating Tables based on groups of related database records

I have a collection of records in my database that I want to print out to separate tables based on the record date. So I already have the following (in Haml, fyi): %table %tr %th Name %th Type %th Total Hits - for record in @records %tr{ :class => cycle('odd','even') } %td= record.name %td= record.target...

MySQL Selecting wrong column value in Group By query

Here's a real noobish MySQL query problem I'm having. I have a high score table in a game I'm writing. The high score DB records a name, level, and score achieved. There are many near duplicates in the db. For example: Name | Level | Score | Timestamp (key) Bob 2 41 | 1234567.890 Bob 3 15 | 1234568.890 Bob 3 ...

MySQL - Optimize a Query and Find a rank based on column Sum

I have a high score database for a game that tracks every play in a variety of worlds. What I want to do is find out some statistics on the plays, and then find where each world "ranks" according to each other world (sorted by number of times played). So far I've got all my statistics working fine, however I've run into a problem findin...

Group by 'other', where groups with small contribution are included in 'other' field - MySQL

I would like to GROUP BY an item in my table, which has an associated monetary value. This I can work out as a percentage, however there are often too many items in the list (too many to be useful, for example in a graph). Consequently, I would like to put those items that have a contribution to the total of less than say 5% into an 'Oth...

Group related records, but pick certain fields from only the first record

I'm preforming an aggregate function on multiple records, which are grouped by a common ID. The problem is, I also want to export some other fields, which might be different within the grouped records, but I want to get those certain fields from one of the records (the first one, according to the query's ORDER BY). Starting point exampl...

Aggregate functions in ADO.NET with GROUP BY functionality

This is a more direct question stemming from an earlier more general question i had earlier now that I've spend more time looking into ADO.NET I want to take an ADO.NET DataTable and perform the equivalent of a SQL SELECT query with aggregate functions (such as SUM) on some columns, and GROUP BY set for the remaining columns. I then wan...

returning partially distinct/unique rows

Hi all, I need to create a query which groups by two columns and returns an additional column based on a condition. For example, say I've got the following columns: ProductCode | SerialNumber | Quantity | DatePurchased | CustomerID and the table contains duplicate combinations of ProductCode and SerialNumber with differing Quanitite...

How to use Linq to group every N number of rows

Hi, I cannot find a way to make this work and hoping someone has an idea. A simplified example would be having a list of say integers 1-100, i want to group every 3 rows so the result would be 1,2,3 in first group then 4,5,6 in next group etc. I know how to get every nth record but what I need is all the records so I can then aggregat...

linq group by a start time to endtime on DateTime Column

Ok this is kinda a messy query and I am only having limited success with it. I have a list of a Foo class that has a datetime property and other data. I have a class/row for allmost every minute, with some missing and some entire days missing like a holiday or weekend. My goal is to group each day for all rows from a start time to an ...

Applying LINQ to Objects Group By and Sort By to generic List<T> (C#)

I am having a hard time creating working Group By and Sort By clauses against a generic List myList. myList has a list of property 'Settings' which itself contains a list of 'child' properties for each business. I want to group by a Industry and within each Industry, sort by business name. My intent would be this: string groupSetting...

MySQL - show field value only in first instance of each grouped value?

I don't think this is possible, but I would like to be proved otherwise. I have written a simple report viewing class to output the results of various database queries. For the purpose of improving the display, when I have a report with grouped data, I would like to display the field value only on the first row of each unique value - an...