group-by

Counting total amount of anwsers from polls in MySQL

I tried different approaches but even though I think my syntax is ok the query doesn't work. I have a fairly complicated polls system. In general every answer to poll is putted into one table CREATE TABLE `polls` ( `poll_id` int(10) NOT NULL auto_increment, `client_id` int(10) NOT NULL, `key` varchar(20) NOT NULL, `val` varchar...

Group by variable integer range using Linq

I'm trying to group a set of data based on the range of an integer, by the range does not increase at a fixed interval. e.g. I have Item ID Price 1          10 2          30 3          50 4          120 I would like to group the items with price 0 - 10, 11- 100, and 100-500. So that item 1 ...

Using SetParameterList and the LIKE keyword

I'm using NHibernate and I have a query which I want to run, which involves returning the whole table plus a count and group by. From what I've read online you can't do this with NHibernate Criteria. To get round this, I'm using a named query: SELECT id, COUNT(id) AS myCount FROM foo INNER JOIN bah ON foo.id = bah.fooId...

LINQ to Entities and grouping with many-to-many association.

I have a Product and Category entity in many-to-many association. I am trying to display the category count for each product. So far, I have come up with this: Dim context = new Context() Dim products = context.Products() Dim productsByCategoryCount = From product In Products Group product By productId = product.productId Into produc...

[SQL] Getting the sum of several columns from two tables

I want to get the sum of several columns from 2 different tables (these tables share the same structure). If I only consider one table, I would write this kind of query: SELECT MONTH_REF, SUM(amount1), SUM(amount2) FROM T_FOO WHERE seller = XXX GROUP BY MONTH_REF; However, I would like to also work with the data from the ...

Showing all duplicates, side by side, in MySQL

I have a table like so: Table eventlog user | user_group | event_date | event_dur. ---- ---------- --------- ---------- xyz 1 2009-1-1 3.5 xyz 2 2009-1-1 4.5 abc 2 2009-1-2 5 abc 1 2009-1-2 5 Notice that in the above samp...

LINQ C# - Combining multiple groups

LINQ Groupby query creates a new group for each unique key. I would like to combine multiple groups into a single group based on the key value. e.g. var customersData = new[] { new { id = 1, company = "ABC" }, new { id = 2, company = "AAA" }, new { id = 3, company = "ABCD" }, new { id = 4, company = "XYZ" }, new {...

Is it possible to perform a bitwise group function?

I have a field in a table which contains bitwise flags. Let's say for the sake of example there are three flags: 4 => read, 2 => write, 1 => execute and the table looks like this*: user_id | file | permissions -----------+--------+--------------- 1 | a.txt | 6 ( <-- 6 = 4 + 2 = read + write) 1 | b.txt | 4 ...

4 table join so that only 1 response is given for each company instead of a result for each campaign

Thanks for your help! I'd like to output all companyName entries that have uploads across any of their serverFiles as: companies.companyName - count(files.fileID) - sum(serverFiles.uniqueUploads) Initech Ltd. - 11 - 24931 Epiphyte Inc. - 23 - 938821 Here are the relavent parts of my table structure: Table: companies c...

MySQL SELECT most frequent by group

How do I get the most frequently occurring category for each tag in MySQL? Ideally, I would want to simulate an aggregate function that would calculate the mode of a column. SELECT t.tag , s.category FROM tags t LEFT JOIN stuff s USING (id) ORDER BY tag; +------------------+----------+ | tag | category | +-------...

silverlight 3 datagrid drag-drop column vertically

Is it possible to allow a datagrid column to be dragged vertically and dropped into an area above the datagrid which triggers it to group by that column? If so, how would i best implement this into a silverlight 3 application? Heres an example of what it could look like: http://www.syncfusion.com/content/en-US/products/feature/windows-f...

SQL conditional GROUP BY: how to do it?

Let's say I have the following SQL query: SELECT Meeting.id AS meetingId, Bill.id AS billId FROM Meeting LEFT JOIN Bill ON Meeting.FK_BillId = Bill.id That outputs the following: meetingId | billId ------------------ a | NULL b | NULL c | 1 d | 1 e | 1 f | 2 g ...

SQL: COUNT() with items appearing multiple times in one-to-many relationship? Alternative to GROUP BY?

I have a query listing product categories, by supercategory > category. The query counts the number of products in each category (COUNT(ptc.catid)). Howewer: the query doesn't let a category appear twice (a category can be linked to multiple supercategories). How can this be re-written so categories (c.title) can be listed under multip...

SQL SELECT order by 2 columns and group by

Here're the RS return and the sql issued, SELECT *, (UNIX_TIMESTAMP(end_time) - UNIX_TIMESTAMP(start_time)) AS T FROM games WHERE game_status > 10 ORDER BY status, T; game_id, player_id, start_time, end_time, score, game_status, is_enabled, T 65, 22, '2009-09-11 17:50:35', '2009-09-11 18:03:07', 17, 11, 1, 752 73, 18, '2009-09-11 18...

sql statement with group by

hi, after a very long sql query a have a result that is in this form: ---col1---col2---col3---col4 ---1234---1------aaaa---bbbb ---2378---0------aaaa---bbbb ---9753---1------cccc---uuuu ---1234---0------iiii---yyyy ---2378---1------iiii---yyyy ---9753---1------tttt---mmmm but i don't need it this way. i have to do another sql-statemen...

Grouping with operands question

I have a table: mysql> desc kursy_bid; +-----------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-----------+-------------+------+-----+---------+-------+ | datetime | datetime | NO | PRI | NULL | | | currency | varchar(6) | NO | PRI | NULL | | | va...

Can I limit the amount of rows to be used for a group in a GROUP BY statement

Hi everyone, I'm having an odd problem I have a table with the columns product_id, sales and day Not all products have sales every day. I'd like to get the average number of sales that each product had in the last 10 days where it had sales Usually I'd get the average like this SELECT product_id, AVG(sales) FROM table GROUP BY prod...

ssrs combining rows into result set

So I have a sql server 2005 query that returns results like so: Address | Doctor ----------------------- 1 Dr A 1 Dr B 1 Dr C 2 NULL 3 NULL 4 Dr D 4 Dr E 5 Dr F What I want is to get the output so that when I render the report I have them grouped together: Dr A Dr B Dr C...

How does MySQL decide which id to return in group by clause?

Such as this one: SELECT id, count( * ) , company FROM jobs GROUP BY company Where id is the primary key of jobs ...

Group by string optimization

I'm gonna convert string to integer to optimize group by performance. What do you guys think of the idea? If applicable,then is there a built-in function to convert string to a unique integer in PHP? ...