0
votes
1answer
19 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
2answers
59 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
32 views

Need to find the employees who have switched jobs atleast twice

I am trying the following query on the tables which are described below: SELECT * FROM job_history INNER JOIN employees ON job_history.employee_id = employees.employee_id WHERE employee_ID IN ...
0
votes
1answer
39 views

How do I solve this problem of a correlated subquery

We have an employee table with the following attributes : employee_id first_name last_name email phone_number hire_date job_id salary commission_pct manager_id department_id Display the manager ...
1
vote
1answer
52 views

mysql optimize group by having count

I have 2 mysql table: table t1: CREATE TABLE IF NOT EXISTS `t1` ( `id` bigint(20) NOT NULL, `url` varchar(255) DEFAULT NULL, `title` varchar(255) DEFAULT NULL, `type` varchar(255) DEFAULT ...
0
votes
2answers
61 views

How do I add an alias to the table

SELECT MAX(COUNT), A.id FROM ( SELECT supplier_id as id, COUNT(*) AS count FROM `library`.`lms_book_details` GROUP BY supplier_id ) GROUP BY A.id; While executing the query, MySQL gives ...
0
votes
1answer
32 views

Group by reversible pair

I'm sure my title is not clear enough... Let's say I have a "chat" table, storing individual messages like this: message_id, from_user_id, to_user_id, timestamp For a given user_id, I want a list ...
0
votes
3answers
104 views

How to query for 'First showed up as'

Given the following table: Date Trd (INT(10)) Code (VARCHAR(10)) T-Date 1-Jan-14 1 B 1-Nov-13 1-Jan-14 2 B 3-Oct-13 ...
0
votes
0answers
80 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 ...
1
vote
1answer
21 views

SELECT showing two COUNTs

I had this table: tblA id | name | value 1 nameA 1 2 nameA 0 3 nameB 0 4 nameC 0 5 nameC 1 6 nameC 1 As you see, Value can be 1 or 0 I need to perform a query ...
0
votes
1answer
42 views

How to remove duplicates in col1 and get group MAX in col2? / mysql

Given the following table: col1 Col2 A 1 B 1 C 1 B 2 B 3 A 0 C 5 how do I write a SELECT query (I just assumed has to be SELECT) that returns the unique items of col1 and the ...
0
votes
1answer
56 views

How to UPDATE MAX and MIN of a GROUP BY?

I have a SELECT query of a table with GROUP BY id col1 category 1 some 1 2 x 1 3 another 1 4 x 2 // should be removed 5 some 2 6 x ...
3
votes
4answers
140 views

Select rows based on latest date with multiple joins

I have this query (SQLFiddle): SELECT c.name, a.user_id, a.status_id, a.title, a.rtime, u.user_name, s.status_name FROM company c LEFT JOIN action a ON a.company_id=c.id LEFT JOIN user u ON ...
0
votes
0answers
39 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?
1
vote
2answers
78 views

Duplicates while doing multiple joins

I have 4 MySQL tables 1. companies - {id*, company} 2. employees - {id*, fname, lname, id_company*} 3. technologies - {id*, technology} 4. company_technologies - {id_technology*, id_company*} Field ...
0
votes
0answers
66 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 ...
2
votes
2answers
113 views

Optimize query or model, to get an population overview in time

I think I've got a very simple model and query, but I cannot find a way to make use of indexes to boost performance. I wonder if there is a trick in my model, or MySQL query, to speed up the ...
1
vote
1answer
54 views

Do id columns help (in speed) select statements?

I have a table which contains data from aggregation software. The columns mostly are int columns, but three of them are string columns. It looks sort of like: userId, someData1, someData2, ...
0
votes
2answers
35 views

Calculating the number of times a group occures

I'm trying to figure out how many times in my database a particular grouping has occurred. I have two relevant tables. mysql> describe logins; ...
1
vote
0answers
69 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 ...
0
votes
0answers
37 views

Ranking MySQL, group by, with ties [duplicate]

I am trying to rank some MySQL results and this works quite well (from here): SET @rank := 0; SET @prev := NULL; UPDATE rankings, (SELECT @rank := IF(@prev = subset, @rank + 1, 1) AS rank, ...
1
vote
1answer
47 views

MySQL Grouping Order

Is it possible to force the order in which a result set is grouped? I have a table with multiple languages in it, and I'm doing a GROUP_CONCAT to get a comma separated list of the product name in each ...
-3
votes
1answer
3k views

How to get MAX and MIN in GROUP BY query?

In a simple query SELECT col1, col2, col3 FROM table1 GROUP BY col1 each row includes the first value of col2 and col3 for each unique value of col. How to select MAX and MIN for each value of ...
0
votes
1answer
122 views

Need to find the number of employee who are not in all department

i have a table T1 which has two columns empname , department every emp must be in 37 department , but there are some employee who are in less than 37 department . I need to find the names of emp who ...
2
votes
1answer
152 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
1k 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
183 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
1answer
121 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
1k 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 ...
2
votes
4answers
263 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
399 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` ...
0
votes
1answer
400 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 ...
2
votes
1answer
3k 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 ...
6
votes
1answer
273 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
259 views

MySQL check duplicate with group by using wildcard?

+----+--------------+-----+-----------+----------+ | ID | NAME | AGE | ADDRESS | SALARY | +----+--------------+-----+-----------+----------+ | 1 | Ramesh Olive | 32 | ...
2
votes
2answers
2k 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(*) ...
1
vote
1answer
929 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
484 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
236 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 ...