The group-by tag has no wiki summary.
2
votes
2answers
91 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
53 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
0answers
28 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
52 views
WITH ROLLUP WHERE NULL = x
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
73 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
89 views
Error “column does not exist” in a SELECT with JOIN and GROUP BY querry
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
130 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
74 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 ...
6
votes
6answers
394 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
110 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
52 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
107 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
196 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
149 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
227 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
767 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
117 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
92 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
230 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
418 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
172 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
227 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
155 views
MySQL check duplicate with group by using wildcard?
+----+--------------+-----+-----------+----------+
| ID | NAME | AGE | ADDRESS | SALARY |
+----+--------------+-----+-----------+----------+
| 1 | Ramesh Olive | 32 | ...
1
vote
2answers
841 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
400 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
34 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
459 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
91 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
425 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
76 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
192 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
196 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
176 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
999 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 ...
