5
голосов
3ответов
8401 просмотров

Select, group and sum results from database

I have a database with some fields I'd like to sum. But that's not the big problem, I want to group those fields by the month they were created. ActiveRecord automaticaly created a field named "created_at". So my question; how can I group the result by month, then sum the fields for each month? ...

0
голосов
1ответов
318 просмотров

How can I use Group By (instead of self-joins) to get correct data (specific example included)

Looking at the related questions, I don't think this specific question has been asked, so here goes. I had a situation where I joined on a table three times to get different data based on dates. This took too long, so in an effort to optimize, I rewrote it using a group by as defined here: http...

7
голосов
2ответов
8645 просмотров

MySQL Group By with top N number of each kind

I have a table like this: Rank Letter 1 A 2 A 3 B 4 A 5 C 6 A 7 C 8 C 9 B 10 C And I need the top 2 of each letter ordered by ascending rank: Rank Letter 1 A 2 A 3 B 5 C...

7
голосов
6ответов
12141 просмотров

SQL to produce Top 10 and Other

Imagine I have a table showing the sales of Acme Widgets, and where they were sold. It's fairly easy to produce a report grouping sales by country. It's fairly easy to find the top 10. But what I'd like is to show the top 10, and then have a final row saying Other. E.g., Ctry | Sales ==========...

1
голосов
3ответов
4791 просмотров

How to make LEFT JOIN work with grouping/count in MySQL?

I have a query like this: SELECT type.id, type.name, COUNT(*) AS tot FROM page LEFT JOIN type ON page.type=type.id GROUP BY type.id However, this doesn't select all the types: it misses out any types that are not in the page table yet. I just want it to list every type with a number representi...

1
голосов
2ответов
169 просмотров

Grouping records with or without a foreign key constraint

I have a table containing objects, something like this: PK ObjectId FK ObjectTypeId Description etc The objects need to be grouped. I have been given a number of suggestions, all of which 'work' and some I like more than others. None of them are perfect so I'm struggling to settle on any par...

2
голосов
2ответов
3941 просмотров

How to query Open-high-low-close (OHLC) data from SQL Server

I'm trying to retrieve data for a Open-high-low-close (OHLC) chart directly from the database, it's the kind of chart you see of stocks. Is this possible, and if, how? I have a table like this (simplified): Date | Price | PriceType A record is created for each day, I will report per month / y...

0
голосов
1ответов
893 просмотров

has_many and sum named_scope

I have this situation: Stories has many Tasks Tasks have an integer called hours_left I need a named scope to find Stories which all its tasks has more than 0 hours left. Based on this post. I wrote this: class Story has_many :tasks named_scope :uncompleted, { :joins=>["INNER JOI...

11
голосов
3ответов
8312 просмотров

LINQ to DataSet case insensitive group by

I have a data table and I want to perform a case insensitive group by over a column of data table (say Column1 of type string). I observed that normally LINQ to DataSet performs a case sensitive comparison. For example, if Column1 has two string values "Test" and "test", after applying group by i...

1
голосов
2ответов
373 просмотров

SQL to show all entries within a number of categories

I'm writing an sql query on three tables which finds and displays categories and all the entries within each category For example Category 1 post 1 post 2 post 3 post 4 Category 2 post 5 post 6 Category 3 post 7 post 8 etc I have the categories displaying but can only get one item from each...

0
голосов
3ответов
366 просмотров

Group by and where?

I'm trying to calculate some columns in a mysql database with this code: "SELECT SUM(klick) FROM svar GROUP BY pollid HAVING pollID="& rstPoll("PollId") But it doesn't work. So what I want to do is to get the sum of all "klick" where a pollId has a certain value. I got this code to work wi...

1
голосов
2ответов
638 просмотров

Having trouble with SQL COUNT

I'm using MS SQL 2008 and I have a table of statuses (id, name) and a table of items (id, name, statusid, deleted). I want to count the number of items with each status, and have the following query: SELECT status.id, ISNULL(COUNT (items.name), 0) AS 'count' FROM status LEFT OUTER JOIN items ON...

8
голосов
4ответов
5189 просмотров

Filling in missing dates using a linq group by date query

I have a Linq query that basically counts how many entries were created on a particular day, which is done by grouping by year, month, day. The problem is that because some days won't have any entries I need to back fill those missing "calendar days" with an entry of 0 count. My guess is that th...

0
голосов
2ответов
223 просмотров

Get percentage value of GROUP BY results in MySQL

I'm working with survey data. Essentially, I want to count total responses for a question for a location, then use that total to create what percentage of all responses each particular response is. I then want to group results by location. An ideal output would be similar to this: Q1 | State...

0
голосов
2ответов
300 просмотров

Group date as month

I have an SQL timestamp field and I need to group it by month like Jan, Feb, Mar, ... but in numbers (01, 02, 03, ...) I've tried GROUP BY DATE_FORMAT(i.push_date, '%Y-%m') But it's not working.

0
голосов
3ответов
230 просмотров

How do I query for records with multiple values for the last column in a compound GROUP BY clause?

Given data that looks similar to this: +---------+-----------+----------+ | country | city | district | +---------+-----------+----------+ | Japan | Tokyo | 1 | | Japan | Tokyo | 1 | | Japan | Tokyo | 2 | | China | Shanghai | A | | China | ...

0
голосов
2ответов
124 просмотров

How can I get the IDs of the rows that make up a GROUP BY ... HAVING query?

I have the following query that looks for transactions that cancel themselves out from the same customer (Some transactions are negative). SELECT c, ABS(r) magnitude, SUM(r) total, COUNT(*) num FROM table GROUP BY c, magnitude HAVING num > 1 AND total = 0 ORDER BY total The result of this q...

1
голосов
2ответов
9898 просмотров

C# List - Group By - Without Linq

I have an object: IObject { string Account, decimal Amount } How do I group by Account and Sum the Amount, returning a List without Linq. 2.0 Framework ... that is why no Linq. Here is what I have: ListofObjects = List<IObject>; foreach (var object in objects) { ...

1
голосов
2ответов
2781 просмотров

Oracle group by and the empty resulting set

I have the following SQL problem. Scenario: I have two tables: Change and ChangeTicket. There is a 1:n relationship. One Change has cero or many changeTickets. No change means no changeTickets. A changeTicket has a status (open/closed/...) A changeTicket has a field representing how many minute...

1
голосов
1ответов
1743 просмотров

How to get away with non-grouping field in HAVING clause

When executing in ONLY_FULL_GROUP_BY mode, I get the error "non-grouping field 'distance' is used in HAVING clause" when executing the following query. The query counts the amount of hotels that are within 15 km distance of a certain latitude & longitude. Is there a way to rewrite this query ...

12
голосов
2ответов
13833 просмотров

Retrieve rows grouped by hour with MySQL

I have a table containing access logs. I want to know how many accesses to resource_id '123' occured in each hour in a 24 hour day. My first thought for retrieving this info is just looping through each hour and querying the table in each loop with something like... and time like '$hour:%', give...

21
голосов
7ответов
56865 просмотров

SQL group by day, with count

I've got a log table in SQL Server that looks like this: CREATE TABLE [dbo].[RefundProcessLog]( [LogId] [bigint] IDENTITY(1,1) NOT NULL, [LogDate] [datetime] NOT NULL, [LogType] [varchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL, [RefundId] [int] NULL, [RefundTypeId] [smallint] NUL...

1
голосов
3ответов
590 просмотров

MySQL: grab one row from each category, but remove duplicate rows posted in multiple categories

I have a database of articles, which are stored in categories. For my homepage, I want to grab an article from each category (I don't care which). However, some articles are crossposted to multiple categories, so they come up twice. I have a table called tblReview with the article fields (revie...

0
голосов
3ответов
740 просмотров

"Indexing" (aka maintaining a table of) aggregate data in SQL Server 2005

I have a table which maintains performance data of a system, each record is a call made to some important method and consists of the method name, its duration and a token - each request to the system is given a unique token and so all of the records with the same token are the same request, e.g: ...

115
голосов
1ответов
9805 просмотров

Linq to Objects: does GroupBy preserve order of elements?

Does Enumerable.GroupBy from LINQ to Objects preserve order of elements in the groups?

0
голосов
1ответов
309 просмотров

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?

3
голосов
1ответов
266 просмотров

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

0
голосов
3ответов
1146 просмотров

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 h...

1
голосов
4ответов
262 просмотров

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

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(sale...

0
голосов
2ответов
121 просмотров

Grouping with operands

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