group-by

postgres group by aggregate function

I have a messages table which looks like this: +------------+-------------+----------+ | sender_id | created_at | message | +------------+-------------+----------+ | 1 | 2010-06-14 | the msg | | 1 | 2010-06-15 | the msg | | 2 | 2010-06-16 | the msg | | 3 | 2010-06-14 | the msg | +----------...

Modifying the group by column to get the newest result on top

Hey I am sorry if this has been already posted or its on internet. I came here after long search Suppose this is the table: +----+-------+----------+---------------------+ | id | name | group_id | created_time | +----+-------+----------+---------------------+ | 1 | foo | 1 | 2010-09-22 00:00:00 | | 2 | rafi | 2 ...

Mysql Query Optimization (Group_Concat)

SELECT b.isbn, booktitle, price, description, GROUP_CONCAT(c.contributorid SEPARATOR '|' ) AS contributorids, GROUP_CONCAT(fname, ' ', lname SEPARATOR '|' ) AS contributornames FROM (book AS b LEFT JOIN book_contributor AS bc ON b.isbn = bc.isbn) LEFT JOIN contributor AS c...

mysql grouping question

Hey guys, quick question. I have a table of messages. There are two types of messages: Messages with a unique group id autonomous messages Messages with the same group id as at least one other message - these I want to group together when they are presented. The grouped messages are messages with replies to them. What I want to do...

Create a pivot-table-style overview of newest items per group

I have a table that stores "comments" on "parent" items in various "languages", with the "date" (dateTime) it was added. Example data: DROP TABLE IF EXISTS comments; CREATE TABLE comments(parentid INT, language char(2), body TEXT, date DATETIME); INSERT INTO comments VALUES (1, 'en', 'english comment 1-1', '2010-09-01 11:00:00'), (1, 'e...

mysql many-to-many tag implementation + full tag listing

based on http://stackoverflow.com/questions/1529073/how-to-implement-tagging-system-similar-to-so-in-php-mysql ive made small changes, and have the following image tag implementation: SELECT I.imageId, GROUP_CONCAT(DISTINCT T.tagName SEPARATOR ' '), COUNT(*) FROM Images I INNER JOIN ImageTagMap M ON I.imageId = M.imageId INNER JOIN Ima...

GROUP BY query not ordering as expected

I don't know why I can't make this query work the way I want, I hope you can enlighten me. SELECT e.id, e.num, e.name, s.name as s_name, t.num as t_num, e.show_id FROM s_episodes e LEFT JOIN shows s ON s.id = e.show_id LEFT JOIN s_seasons t ON t.id = e.season_id GROUP BY...

How to use MongoDB Ruby Driver to do a "Group" (group by)?

related to http://stackoverflow.com/questions/2127374/mongodb-group-using-ruby-driver if I want to do something like the following in SQL: select page_id, count(page_id) from a_table group by page_id I thought the MongoDB's doc says http://api.mongodb.org/ruby/current/Mongo/Collection.html#group-instance_method group(key, condition...

MySQL Invalid use of group function on shared host

I need to know the team with highest number of matches in a certain city. SELECT COUNT(am_matches.team_id) AS matches_count, am_matches.team_id AS team_id FROM am_matches LEFT JOIN am_team ON (am_matches.team_id = am_teams.id) WHERE am_matches.status = '3' AND am_teams.city_id = '$city_id' GROUP BY am_matches.team_id ORDER BY COUNT(am_m...

mysql query help : show field Appearance count and Appearance Percentage

What I need is query that show : How many times value in field is Appearance and how many Percentage is from the total. What I have until now is : SELECT field, COUNT(field) FROM table GROUP BY field; That show me result of how many time every value is Appearance. How can I get also the Appearance Percentage in one query ? I try...

Is there any MySQL Aggregate Function for "CONTAINS"?

Say I have this data set user | group --------+------- [email protected] | A [email protected] | B [email protected] | A [email protected] | B [email protected] | A [email protected] | B [email protected] | C I want to convert this into a table like this: user | IN_A | IN_B | IN_C --------+-------+-------+------- [email protected] | TRUE | TRUE | FALSE [email protected] | TRUE | FALSE | FALSE c@...

Effective way to separate a group into individual records.

Hi there, I'm grouping some records by their proximity of time. What I do right now (timestamps in unixtime), First off I do a sub select to grab records that are of interest of me, (SELECT timestamp AS target_time FROM table WHERE something = cool) AS subselect Then I want to look at the records that are close in time to those, S...

MySQL COUNT in two tables same time.

Hello! My question is not so hard, i just really can't find the answer anywhere. I've got 3 tables. webshops, webshop_category and webshop_item. My query is: SELECT webshops.id, webshops.name, webshops.tax, webshops.contact_name, webshops.contact_email, webshops.contact_phone, webshops.contact_address, COUNT(webshop_category.id), CO...

Force Specific Record to Top When Performing GROUP BY

I have the following MySQL query and tables from which I am querying: SELECT `Song`.`id`, `Song`.`file_name`, `User`.`first_name`, `Vote`.`value`, Sum(`Vote`.`value`) AS score FROM `songs` AS `Song` LEFT JOIN votes AS `Vote` ON (`Song`.`id`=`Vote`.`song_id`) LEFT JOIN `users` AS `User` ON (`Song`.`user_id` = `User`.`id`) GROUP BY `...

Python group by

Hello Assume that I have a such set of pair datas where index 0 is the value and the index 1 is the type: input = [('11013331', 'KAT'), ('9085267', 'NOT'), ('5238761', 'ETH'), ('5349618', 'ETH'), ('11788544', 'NOT'), ('962142', 'ETH'), ('7795297', 'ETH'), ('7341464', 'ETH'), ('9843236', 'KAT'), ('5594916', 'ETH'), ('1550003', 'ETH')] ...

MySQL Query - Using Aggregate and Group By to generate separate results

I have a table where one or more entries with the same 'id' value can be inserted into our log / fact table (contains over 100+ million records) At a set frequency a new record is inserted into this table with a new value for the columns 'created' and 'view_percent' (percentage of a video viewed). With two different queries, I would like...

Group by element in Linq

Lets assume we have the following array var arr = new string[] {"foo","bar","jar","\r","a","b,"c","\r","x","y","z","\r"); Also ignore the fact that this is strings, so no string hack solutions please. I want to group these elements by each "\r" in the sequence. That is, I want one array/enumerable with "foo","bar","jar" and another w...

Appropriate SQL Server approach for retrieving this grouped data?

Hi everyone, I have a table (AU_EMPLOYEE) with two columns named EmployeeID (int) and LastModifiedDate (DateTime). Along with those columns are others containing additional employee data. This is an audit table and every time an employee's data changes in some way a new row is added. So it's quite likely a given employee will have mu...

How to relax GROUP BY restrictions in SQL Server?

Consider this query: SELECT F1,F2 FROM TABLE GROUP BY F1 Selecting F1 is valid, but F2 seems to be incorrect (after all it can change from row to row). However SQL Server does not check any logic involved here -- for example F2 could be dependent of F1 (because of the JOIN clause, for example). I know the workarounds, but my questio...

Is combining (result sets) of "two queries with aggregate and group by" possible ?

1 Select id,count(*) as totalX FROM my_table WHERE x_factor = 1 GROUP BY id ResultSet: id totalX --------- -------------- 9 34 10 6 11 21 12 3 2 Select id,count(*) as totalY FROM my_table WHERE y_factor = 1 GROUP BY id ResultSet 2: id...