GROUP BY : SQL Aggregation for Common Values

learn more… | top users | synonyms

13
votes
2answers
2k 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 ...
6
votes
1answer
288 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 ...
1
vote
1answer
67 views

Why am I getting different results from a query vs. a view

The base problem I'm facing is that I need the latest record for each item. A little setup... MySQL 5.6.14. I need to create two views (because MySQL won't let me have a subquery in a view). My ...
8
votes
6answers
3k 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 ...
2
votes
1answer
6k 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 ...
3
votes
4answers
2k 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 ...
2
votes
2answers
4k 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
1answer
210 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 ...
-1
votes
4answers
96 views

Index on 'GROUP BY' with functions

I have a log table. It logs visitor data (time of visit, browser, window size ...). I want to create reports. I use a query like this (MySQL): SELECT COUNT(*) as raw_views ... FROM logs WHERE ...