group-by

Hibernate Criteria API: count and group by with result in Map

The Car entity is mapped to a database table with 2 columns: ID and Color. CarDao has the following method: Map<Color, Integer> countByColor(); If we have 3 red cars and 2 blue cars in the database table, the method returns a map with 2 keys (red and blue) and the corresponding count (3 resp. 2). I would like to do this with the Cri...

LINQ: Selecting items from a list (Group By/Select/Sum & Max!)

Hi, Just getting my head around Linq and having lots of fun! Can any one aid me with a query for this: I have a list of data: Key Value Aaa 12 AaA 10 AAa 5 BBB 2 Bbb 1 1. I want to group by Key.ToUpper() 2. For every group I need the Max(Value) & Sum(Value) 3. For every group I want to select the entries T...

Linq/C#: Selecting and summing items from the result of a group by?

Hi, I have a list like this: City Total Sydney 11 Dublin 9 London 12 Dublin 3 London 9 Sydney 12 I first of all need to Group By City & Sum Total so I have Sydney 23 Dublin 12 London 21 Next I need to filter for those that entries where the total is > 20 Sydney 23 London 21 And what I finally need is the total of these entrie...

Django: Filtering datetime field by *only* the year value?

Hi folks, I'm trying to spit out a django page which lists all entries by the year they were created. So, for example: 2010: Note 4 Note 5 Note 6 2009: Note 1 Note 2 Note 3 It's proving more difficult than I would have expected. The model from which the data comes is below: class Note(models.Model): business = models.Forei...

Group by date range on weeks/months interval

I'm using MySQL and I have the following table: | clicks | int | | period | date | I want to be able to generate reports like this, where periods are done in the last 4 weeks: | period | clicks | | 1/7 - 7/5 | 1000 | | 25/6 - 31/7 | .... | | 18/6 - 24/6 | .... | | 12/6 - 18/6 | .... | or in the last 3 months: ...

MySQL Update query with left join and group by

I am trying to create an update query and making little progress in getting the right syntax. The following query is working: SELECT t.Index1, t.Index2, COUNT( m.EventType ) FROM Table t LEFT JOIN MEvents m ON (m.Index1 = t.Index1 AND m.Index2 = t.Index2 AND (m.EventType = 'A' OR m.EventType = 'B') ...

mysql showing null values for group by statements

I'm doing: select sum(clicks), date from stats group by date ...however when the sum is null for some date, the whole row is discarded, I want to see: | null | some_date |, how to do so? ...

MySQL query problem on group by and max

My table structure is (id,cluster,qid,priority). I'm trying to figure out how I can display the maximum value of priority for each cluster. Say cluster 1 has priorities 100, 102, 105. I want to display the record containing 105. Please help. ...

linq query and results display by category

I want to display list of items which belong to certain category like this: Category I Item 1 Item 2 Item 3 Category II Item 6 Item 7 Category III Item 10 Item 11 Item 12 Item 13 What is the best (and easiest) way to do this? I started doing this with two queries - one gets all goals, and then in foreach loop (in controller)...

SQLAlchemy: How to group by two fields and filter by date

So I have a table with a datestamp and two fields that I want to make sure that they are unique in the last month. table.id table.datestamp table.field1 table.field2 There should be no duplicate record with the same field1 + 2 compound value in the last month. The steps in my head are: Group by the two fields Look back over the las...

Counting the instances of customers

Say that I have a table with one column named CustomerId. The example of the instance of this table is : CustomerId 14 12 11 204 14 204 I want to write a query that counts the number of occurences of customer IDs. At the end, I would like to have a result like this : CustomerId NumberOfOccurences 14 2 12 ...

Does the order of columns matter in a group by clause?

If I have two columns, one with very high cardinality and one with very low cardinality (unique # of values), does it matter in which order I group by? Here's an example: select dimensionName, dimensionCategory, sum(someFact) from SomeFact f join SomeDim d on f.dimensionKey = d.dimensionKey group by d.dimensionNa...

Easy SQL Group-By question. (I'm new)

I am new to SQL so hopefully this has an easy answer. I have a Students table (studentID, name, statusID) and a StudentsClasses table (studentID, classID). I've been asked to create a view from the above tables that returns the following columns: classID: (group-by) count of students in each class AS Students count of students w...

LINQ Group By Subtotal & Total

I have a Batch with BatchItems entered by multiple users. I'm trying to not only get the subtotal per user for a single batch, but also grand total for that same batch regardless of the user grouping. Its this last part that I can't figure out. How might I get that total in order to return it as a list? from b in context.BatchItem ...

JOIN on another table after GROUP BY and COUNT

I'm trying to make sense of the right way to use JOIN, COUNT(*), and GROUP BY to do a pretty simple query. I've actually gotten it to work (see below) but from what I've read, I'm using an extra GROUP BY that I shouldn't be. (Note: The problem below isn't my actual problem (which deals with more complicated tables), but I've tried to c...

Mysql: how to select groups having certain values?

Hello. Say there is such table: mysql> SELECT * FROM tags; +---------+--------+ | post_id | tag_id | +---------+--------+ | 1 | 2 | | 1 | 3 | | 1 | 1 | | 2 | 1 | | 2 | 2 | +---------+--------+ 5 rows in set (0.00 sec) Field names are pretty self-explanatory. I want to select post_...

How and why combination of JOIN and subquery affects GROUP BY behavior in MySQL query?

I have 3 sql tables: Data36 (Data_ID:int <PK>, type:int), Data38(Data_ID:int <PK>, clientId:int), Data47(Data_ID:int <PK>, payerID:int). I thought the following queries are identical, because I don't use aggregate functions here and GROUP BY should behave the same way as DISTINCT. But they return very different result sets and I do...

How to select only one full row per group in a "group by" query?

Hi, I have a (seems to be) very easy problem, but after searching for hours, I can't find anything helpful. Here's the problem: In Microsoft SQL, I have a table where a column A stores some data. This data can contain duplicates (ie. two or more rows will have the same value for the column A). I can easily find the duplicates by doin...

Select all months within given date span, including the ones with 0 values

Hi, I'm trying to write a MySQL query to get an average value per month, for all months between to given dates. My idea is this: Query, something like SELECT AVG(value1) as avg_value_1, AVG(value2) as avg_value_2, MONTH(save_date) as month, YEAR(save_date) as year FROM myTable WHERE save_date BETWEEN '2009-01-01' AND '2009-07-01' G...

Not grouping by date?

I have a database table that stores document information (DocID, Name, Type, Owner, etc). Then I have another table that stores the times the document has been downloaded (DocID, DownloadTime, etc). I'm trying to set up a page for recording the number of hits that different kinds of documents (policies, forms, publications, etc) get in a...