group-by

MySQL group by issue

I'm having a strange problem with MySQL and would like to see if the community has any thoughts: I have a table 'tbl' that contains ____________ | id | sdate | And I'm trying to execute this query: select id, max(sdate) as sd from tbl where id in(123) group by id; This returns no results. However, this query: select id, sdate...

Eliminate duplicate data

Query SELECT ppc.personid, ppc.category, ppc.phonenumber, ppc.createts FROM person_phone_contacts ppc WHERE CURRENT_TIMESTAMP BETWEEN ppc.createts AND ppc.endts AND CURRENT_DATE BETWEEN ppc.effectivedate AND ppc.enddate ORDER BY ppc.personid, ppc.category, ppc.createts DESC Resulting Data 3742 | Home | xxx-xxx...

LINQ to SQL Group by C# problem

I recently started to use LINQ to SQL and i have a minor complex query i need help with. I've got a table in my database called MovieComment, with the following columns: CommentID UserID MovieID Comment Timestamp So, what i wanna do is to group the comments on MovieID and save them into my object called Movie, where the MovieID is be...

Last 12 months, group by week

Hi, I have a table with a column REGDATE, a registration date (YYYY-MM-DD HH:MM:SS). I would like to show an histogram (ExtJS) in order to understand in which period of the years users are signing up. I would like to do this for the past twelve months with respect to the current date and to group dates by week. Any hints? ...

SQL Query: Each GROUP BY expression must contain at least one column that is not an outer reference.

Getting this error: Each GROUP BY expression must contain at least one column that is not an outer reference. RowId ErrorDatetime ErrorNum ErrorMessage 824 2010-09-24 08:01:42.000 9001 Account 55 not found 823 2010-09-24 08:00:56.000 9001 Account 22222222222 not found 822 2010-09-24 05:06:27.000 ...

SQL query to obtain a count of how many items have each possible value for a given column

Okay, so I have a (SQLite) table called log wherein one column (time) is a Unix epoch timestamp (the other columns are irrelevant for my purposes). These are timestamped log entries. I am trying to get a count of how many items are logged per hour (all-time -- I don't care to do it only for a given day or whatever; I'm trying to get an ...

how to put GROUP BY correctly?

How to use grouping correctly in this query: $sQuery = "SELECT id,DATE(A.Inspection_datetime) AS Date, A.Model, COUNT(A.Serial_number) AS Qty, B.name FROM inspection_report AS A LEFT JOIN Employee AS B ON A.NIK=B...

mysql group by confusion

so i have this table; mysql> describe player_weapon_stats; +------------+------------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +------------+------------------+------+-----+---------+----------------+ | id | int(10) unsigned | NO | PRI | NULL |...

Selecting 2 distinct rows MySQL

I have a table called categories that has 3 columns: id, name, parent_id. ID name group_id == == == 1 haim 1 2 gil 2 3 alon 1 4 idan 1 5 shai 3 6 adi 2 7 itay 3 8 aviram ...

MSSQL: Only last entry in GROUP BY (with id)

Hi everyone. Following / copying computhomas's question, but adding some twists... I have the following table in MSSQL2008 id | business_key | result | date 1 | 1 | 0 | 9 2 | 1 | 1 | 8 3 | 2 | 1 | 7 4 | 3 | n | 6 5 | 4 | 1 | 5 6 | 4 | 0 | 4 And now i want to group based on the business_key returning the complete entry with the newest...

Get the last entries using GROUP BY

I'm having problem with GROUP BY. It returns the first entry it could find, but I would like it to return the last entry. Is that possible? Here is my query (prepared query): SELECT stamp_user, stamp_date, stamp_type FROM rws_stamps WHERE stamp_date >= ? GROUP BY stamp_user ORDER BY stamp_date DESC My table looks like this: What I...

mysql grouping problem

Hello folks, I have the following table: date1 date2 sc cash date "2010-09-20" "2010-09-21" 202 300 "2010-03-01" "2010-09-20" "2010-09-21" 202 600 "2010-08-01" "2010-09-20" "2010-09-21" 202 670 "2010-08-20" "2010-09-20" "2010-09-21" 202 710 "2010-09-01" "2010-09-20" "2010-09-21" 202...

One liner to chunk Ruby objects into sub arrays based on Date/Timeframe?

Say I have 1200 ActiveRecord objects with a created_at attribute, and 100 were created each month for a year. What's the one liner ruby way to iterate through the records and chunk them by month? [record_a, record_b, record_c, ...].group_by(&:month) do |month, records_for_the_month| records_for_the_month.each ... end ... assuming I...

XQuery statement that groups results

Hi I have an XML structure that I need to query using XQuery. As far as I have been able to find, this may not even be possible, but since I am so new to XQuery I thought I may ask. I need to query the XML below and return something that looks like: <product> <title>Acer Liquid</title> <image>ACER-LIQUID.jpg</image> <networks> <n...

GROUP BY and ORDER BY in same MySQL query?

I want to select a bunch of data from a table using a GROUP BY clause. This works great, but I need to order the data by the date it was created, with an ORDER BY clause. My question is, can I use both these clauses within the same query, or should I be using one in a sub-query, or something else? The original query (no modification) is...

How should i modify this SQL statement?

My SQL Server view SELECT geo.HyperLinks.CatID, geo.Tags.Tag, geo.HyperLinks.HyperLinksID FROM geo.HyperLinks LEFT OUTER JOIN geo.Tags INNER JOIN geo.TagsList ON geo.Tags.TagID = geo.TagsList.TagID ON geo.HyperLinks.HyperLinksID = geo.TagsList.HyperLinksID WHERE HyperLinksID = 1 returns these... HyperLinksID...

Selecting count of zero while grouping by column

I have a jobs table, and am trying to get a count of jobs for different time frames. My current query looks like this: SELECT COUNT(*) AS 'count', WEEK(j.created_at) AS 'week', MONTH(j.created_at) AS 'month', YEAR(j.created_at) AS 'year', DATE_FORMAT(j.created_at, '%y') AS 'short_year' FROM jobs j WHERE j...

Select with two counts on same column

I have two select queries: SELECT MONTH(date) AS month, count(DISTINCT ip) FROM table_name WHERE field = 1 GROUP BY month and SELECT MONTH(date) AS month, count(DISTINCT ip) FROM table_name WHERE field = 2 GROUP BY month how can I write one query to select SELECT MONTH(date) AS month, count(DISTINCT ip) [ for field = 1],...

Problem using JPA with Oracle and grouping by hour

Hi, I have a problem using JPA with Oracle and grouping by hour. Here's the scenario: I have an entiry named EventData. This entity has a java.util.Date field named startOfPeriod. This field maps in a table field of datatype DATE. The query I'm using is something like: select min(ed.startOfPeriod) as eventDate, (...) from Event e inner ...

How can I group a List<K> by a List<T>, depending on the results of an expression<T, K>?

I'm having a difficult time expressing this problem, so bear with me and please feel free to ask followup questions. I have a Period object, and a function elsewhere that looks like this: public static bool PeriodApplies(Period period, DateTime date) This function takes an object that describes a recurring period (think a schedule sh...