The group-by tag has no wiki summary.
2
votes
1answer
24 views
Is it possible to reduce a group to a row based on some criteria?
I'd like to perform a select on a table involving a GROUP BY such that all rows that share the same set of identifiers are grouped together, but I want to reduce the group to one of the grouped rows ...
-1
votes
2answers
33 views
How to use GROUP BY after the Having clause Mysql
Here is the query which gets the accounts for a specific user the products are related to accounts in one-to-many relation so for each product there are four permissions
All the accounts which is not ...
2
votes
2answers
125 views
Why does adding LIMIT to my query make it crawl?
Simple query:
select sum(score) total,name,gender,dob,country
from users join scores on users.id = scores.user_id
where date between '2012-01-01' and '2012-01-31 23:59:59'
group by scores.user_id ...
1
vote
2answers
72 views
Group Database Entries by time difference
I have a large postgreSQL database with log data. All this data has timestamps and I want to group consecutive rows where the difference between the timestamps is less then 1500 miliseconds for ...
1
vote
1answer
38 views
Repeated values in group_concat
I have two tables, first the table food and Second is Activity:
INSERT INTO food
(`id`, `foodName`)
VALUES
(1, 'food1'),
(2, 'food2'),
(3, 'food3'),
(4, 'food4'),
(5, ...
0
votes
1answer
131 views
WITH ROLLUP WHERE x IS NULL
I tried to find this question somewhere else on here, and found a couple that were similar to it -- but all the answers seemed pretty convoluted, and I think it was surprising to me because I just ...
1
vote
1answer
79 views
GROUP BY with OR/AND clause?
Lets say my database looks like this:
Table: messages
+----+------+----+----------+----------+
| id | from | to | double_1 | double_2 |
+----+------+----+----------+----------+
| 1 | 1 ...
0
votes
1answer
158 views
Error “column does not exist” in a SELECT with JOIN and GROUP BY query
I'm using PostgreSQL 9.1 with a Ruby on Rails application.
I'm trying to list the last version of each "charge" (in my history table : hist_version_charges) belonging to the same project id ...
2
votes
4answers
134 views
Delete the N oldest entries grouped by multiple columns
I´d like to delete the oldest entries of my database, but in each category shall remain at least N words of each char.
Structure of TABLE words
id | word | char | category
For example I have the ...
2
votes
2answers
110 views
Postgres: Use result of group by as column names
I have a messages table in a database, which include a sender id and a message type (and of course many more columns not relevant for this question). I try to create a query which counts how many ...
7
votes
6answers
567 views
Find “n” consecutive free numbers from table
I have some table with numbers like this (status is either FREE or ASSIGNED)
id_set number status
-----------------------
1 000001 ASSIGNED
1 000002 FREE
1 000003 ...
1
vote
1answer
145 views
postgres GROUP BY and ORDER BY problem
I have two tables like this:
CREATE TABLE cmap5 (
name VARCHAR(2000),
lexemes TSQUERY
);
and
CREATE TABLE IF NOT EXISTS synonyms_all_gin_tsvcolumn (
cid int NOT NULL ...
1
vote
1answer
74 views
Avoiding performance hit from GROUP BY during FULLTEXT search?
Is there any clever way to avoid the performance hit from using group by during fulltext search?
SELECT p.topic_id, min(p.post_id)
FROM forum_posts AS p
WHERE MATCH (p.post_text) AGAINST ('baby ...
2
votes
2answers
131 views
Slow MySQL query with Order and Group by
I have a query that is taking a long time to execute (1.13s). The two tables user_articles and articles have around 25,000 records. This is the query:
SELECT `user_articles`.*
FROM `user_articles`
...
3
votes
3answers
257 views
PostgreSQL 8.3: Slow GROUP BY on large table
I have a table with about 10 million records. I want to do a simple group by, but it's using a sequential scan and is slow...
select run_id, count(*) from result group by run_id;
I have an index ...
0
votes
1answer
195 views
Group By on column from joined table
its about the following query.
EXPLAIN SELECT phrase, infinitiv
FROM `dict_verbs_all` `t`
INNER JOIN `dict_verbs_all_language` `verbsGerman_c`
ON (`t`.`id`=`verbsGerman_c`.`verb_id`)
INNER JOIN ...
5
votes
2answers
267 views
T-SQL Purpose of MAX in this Group By Query
I have run into some pre-existing SQL that I'm having a hard time uderstanding.
SELECT
MAX(I.Symbol) Symbol
, MAX(I.Ticker) CUSIP
, MAX(I.Name) Name
, SUM(H.Quantity) TotalQuantity
, ...
2
votes
1answer
1k views
Multiple “GROUP_CONCAT”s with WHERE clause
In a table as
id name type info
1 BMW car yes
2 Reno car no
3 IBM electronics no
4 Sony electronics yes
5 Mazda ...
1
vote
1answer
129 views
Select Count(*) FROM with-clause-named table order by a, b not working
I have a series of tables defined in a stored procedure to eventually return to the application like this, then culminating to a simple table which contains distinct values from a previously defined ...
0
votes
1answer
102 views
Group By - Incorrect syntax near 'mycolumn' [closed]
I'm getting a syntax error in SQL Server Management Studio (SQL Server 2008) for the following query:
SELECT
MIN(EffectiveOn) AS EffectiveOn,
Address1,
Address2,
Address3,
...
3
votes
2answers
383 views
How group with min(date) and select an another column in the same table
I have a query who return something like it :
Name Gender Job date of hire
John M mechanic 2012-05-08
John M electrician 2010-01-01
Vicky F scientific 2012-11-11
Bob ...
2
votes
3answers
491 views
Slow query on large table with GROUP BY and ORDER BY
I have a table with 7.2 million tuples which looks like this:
table public.methods
column | type | attributes
...
2
votes
1answer
180 views
SELECT multiple sensor values in one query
Background
I have a couple of devices, each with a couple of sensors. I log these every now and then and stores them in a table described below. When someone requests a web page, I fetch a couple of ...
4
votes
1answer
240 views
What corner cases exist when relying on undocumented behaviour to determine values selected by MySQL for hidden columns in GROUP BY operations?
Under GROUP BY and HAVING with Hidden Columns, the MySQL manual documents (added emphasis):
MySQL extends the use of GROUP BY so that the select list can refer to nonaggregated columns not named ...
4
votes
2answers
170 views
MySQL check duplicate with group by using wildcard?
+----+--------------+-----+-----------+----------+
| ID | NAME | AGE | ADDRESS | SALARY |
+----+--------------+-----+-----------+----------+
| 1 | Ramesh Olive | 32 | ...
1
vote
2answers
988 views
how to calculate difference of first row and last row fields value in each group
I have table with structure like this:
+-------+------------------+
| Value | Date |
+-------+------------------+
| 10 | 10/10/2010 10:00 |
| 11 | 10/10/2010 10:15 |
| 15 | ...
1
vote
2answers
558 views
GROUP BY two columns
I want to count two columns, but need to get the results of first column in one row.
SELECT country, COUNT(*)
FROM table1
GROUP BY country, type
This query gives me
country type COUNT(*)
...
0
votes
0answers
35 views
Group by collection_item_id and order by products.weight
SELECT
`products`.*
FROM `products`
INNER JOIN collection_items ON products.collection_item_id = collection_items.id
WHERE `products`.`product_type_id` = 1
AND (products.status = ...
0
votes
1answer
659 views
Need to count records and group count by date on Oracle
I have a table like the following
ID created sent type
-----------------------------------------------------
0001463583000051783 31-JUL-12 1 270
0081289563000051788 ...
4
votes
1answer
94 views
GROUP BY on a dependent field
Let's say I have a SELECT statement as follows:
SELECT ..., field1
FROM ...
GROUP BY field1
Now let's say I want to return a second field, field2. As it happens, field2 is functionally dependent ...
1
vote
1answer
465 views
Slow complex query with group/order
I have a problem with a very important query in my application.
It is very slow when adding group and order to it.
So the full query is:
SELECT
m.id,
m.title,
m.title_text,
...
0
votes
2answers
79 views
Grouping data checking against top values
I've got data in this form:
CallNum Value Accepted
--------------------------------------
971374 81.482204444473609 True
971374 83.783551111089764 False
971374 97.936875555547886 ...
3
votes
2answers
217 views
Pivoting data for stacked charts (grouping and creating new columns for each distinct value)?
I have something that looks like this (SQL Fiddle here):
Date Location Value
1/1/2012 Baltimore 36
1/1/2012 Houston 36
1/1/2012 Chicago 55
2/1/2012 Baltimore 49
...
0
votes
2answers
231 views
limit and then group by in MySQL
I have a mysql table having following structure :
payment_id(int auto_increment),
emp_id(int),
chargeTime(datetime),
payment(double)
I need to have three latest (with respect to chargeTime) ...
1
vote
0answers
184 views
MySQL GROUP BY and ORDER BY giving inconsistent results
I'm currently working with WordPress, and I've added a new meta value which is a number between 0 and 30. Zero is ignored and up to 30 need to be organised in order and only the latest (based on the ...
11
votes
2answers
1k views
Why do wildcards in GROUP BY statements not work?
I am trying to make the following SQL statement work, but I get a syntax error:
SELECT A.*, COUNT(B.foo)
FROM TABLE1 A
LEFT JOIN TABLE2 B ON A.PKey = B.FKey
GROUP BY A.*
Here, A is a wide table ...
