GROUP BY : SQL Aggregation for Common Values

learn more… | top users | synonyms

0
votes
2answers
32 views

sqlite group_concat error while executing query

I have table: CREATE TABLE www ( Col_1 VARCHAR, Col_2 VARCHAR ); select count(*) from www; --1496 And I have error: "Error while executing query: aggregate function are not allowed in ...
0
votes
1answer
40 views

How to optimize a very slow query with joins and group by?

I have this GTFS database : All the ***_id columns are indexed, and there are foreign keys between tables (such as a FK on trips.route_id to routes.route_id). So basically the link between a stop ...
0
votes
1answer
21 views

MySQL order of query

My query return like this. 12 14 14 99 78 78 54 I want result to be sorted so that no same number comes consecutively in the result. The result that I want is like this. 12 14 99 14 78 54 78
0
votes
1answer
67 views

sql server Group by with calculated values

I have two tables: costumer table and invoice table. How to calculate sum(value) for each costumer (id) when I have calculated variables in group by statement - without puting code for calculated ...
0
votes
1answer
76 views

GROUP BY performance

I have a query like this: SELECT HOUR(datetime) as Hour, COUNT(*) as hits, SUM(clicks) FROM visitors_log_table WHERE datetime >= CURDATE() GROUP BY Hour WITH ROLLUP It works fine. But I ...
-1
votes
1answer
63 views

Select latest record for each ID

I have three tables that contains data as below: Users Id Name Other_Columns --------------------------- 1 John Blah 2 Ricky Blah 3 Stella Blah 4 Bob Blah Saldo Id ...
5
votes
0answers
250 views

Should I group by and then join, or join and then group by?

(oracle's optimizer does the same thing for both approaches when just two tables are involved, thus my question involves three tables) Assume three tables t1, t2 and t3 with a pairwise 1:n ...
1
vote
0answers
63 views

GROUP BY multiple columns

I'm trying to draw stats out of a table and depending on the increment, group them by year, month, date. The table is as follows CREATE TABLE `channel_stats` ( `channel_id` int(10) unsigned ...
1
vote
0answers
88 views

How to index for a group by with columns from different tables?

Is it possible to create an index from columns of 2 different tables? If not, how do we optimize a query that does a group by on columns of 2 different tables? For example: the following ...
1
vote
0answers
272 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 ...
0
votes
0answers
28 views

Mysql sum of column group by question

I have the results of a select that I am joining on that I need to get a slightly complex sum of a column from. I need the sum of result grouped on id PLUS the sum of result of any row where ...
0
votes
0answers
36 views

Selecting most recent date when dates are stored in another table

Working in a MySQL database, I have tables set up like this: Table song_instances has song_id (int), meeting_instance_id (int) The meeting_instances table has columns meeting_instance_id (int), ...
0
votes
0answers
41 views

mysql select latest per group using 2 different tables along with self join

I have a query SQL Fiddle which contains 2 tables tbl_data CREATE TABLE tbl_data ( id int(10) unsigned NOT NULL, type varchar(32) NOT NULL DEFAULT '', title varchar(255) NOT NULL DEFAULT ...
0
votes
0answers
24 views

Generate report from visitor counter

I have a visitor counter, the table looks like this: id | country_code | datetime | Browser | ... --------------------------------------------------------- 1 | FR | ...
0
votes
0answers
239 views

group by and order by making query very slow in mysql?

Query works fast: select SQL_NO_CACHE users.id, users.name from useres left join user_statistics on users.id = user_statistics.user_id where users.is_deleted = 0 group by users.id ...
0
votes
0answers
41 views

Is it possible to group by a fraction of the result set?

I have a database of products with prices. What I'd like to be able to do is figure out 5 different price ranges such that each range, when ordered by price, will have approximately the same number of ...
0
votes
0answers
47 views

How can I number my groups using an Access “Find Duplicates” Query?

I have a query created by the MS Access 2010 find duplicates wizard. It is: SELECT * FROM fbExpenses WHERE [expense_date] In (SELECT [expense_date] FROM [fbExpenses] As ...
0
votes
0answers
124 views

Select Foo object without any specific Bar events

Consider two tables: Foo: id INT, name VARCHAR Bar: id INT, foo_id INT REFERENCES Foo(id), event_type VARCHAR DEFAULT NULL, event_duration INT DEFAULT NULL Each Foo item can have ...
0
votes
0answers
70 views

is it good to add index on column used in group by clause?

I have a query like- select x, sum(y) from xyz where z>=100 group by x order by sum(y) desc; will it be faster if I am going to add index on x?
0
votes
0answers
80 views

can this mysql query be optimized further?

I have this query select counterparty_id, max(id) max_id from client_administrations where counterparty_type = 1 and effective_on <= '2013-10-22' group by counterparty_id When I ...