GROUP BY : SQL Aggregation for Common Values

learn more… | top users | synonyms

0
votes
2answers
100 views

How do I group the data in the following format?

Using the following table OrderDate OrderID [dates] [id] I want to display in the following format : quater name nooforders q1 10 q2 10 q3 20 I ...
0
votes
1answer
30 views

PostgreSQL : select columns inside json_agg

If I have a query like SELECT a.id, a.name, json_agg(b.*) as "item" FROM a JOIN b ON b.item_id = a.id GROUP BY a.id, a.name; How can I select the columns in b so I don't have b.item_id in the ...
0
votes
1answer
34 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 ...
3
votes
0answers
202 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
76 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
245 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
12 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
66 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
34 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
29 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
116 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
49 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
76 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 ...
-1
votes
0answers
26 views

Group by not working in MySQL

I have Dimension table (Dim_Store)with 224 records and fact table with 256 M rows. If i fire the below query, it gives me only one record, which is absolutely fine. SELECT s.store_code, ...