All Questions
10,350
questions
1512
votes
3
answers
2.2m
views
Using group by on multiple columns
I understand the point of GROUP BY x.
But how does GROUP BY x, y work, and what does it mean?
- 59.6k
1325
votes
34
answers
1.2m
views
Retrieving the last record in each group - MySQL
There is a table messages that contains data as shown below:
Id Name Other_Columns
-------------------------
1 A A_data_1
2 A A_data_2
3 A A_data_3
4 B ...
- 27.2k
798
votes
19
answers
896k
views
MySQL Query GROUP BY day / month / year
Is it possible to make a simple query to count how many records I have in a determined period of time like a year, month, or day, having a TIMESTAMP field, like:
SELECT COUNT(id)
FROM stats
WHERE ...
- 12.8k
509
votes
18
answers
736k
views
Error related to only_full_group_by when executing a query in MySql
I have upgraded my system and have installed MySql 5.7.9 with php for a web application I am working on. I have a query that is dynamically created, and when run in older versions of MySQL it works ...
- 5,365
481
votes
14
answers
430k
views
How to concatenate strings of a string field in a PostgreSQL 'group by' query?
I am looking for a way to concatenate the strings of a field within a group by query. So for example, I have a table:
ID
COMPANY_ID
EMPLOYEE
1
1
Anna
2
1
Bill
3
2
Carol
4
2
Dave
and I wanted to ...
- 7,080
471
votes
7
answers
779k
views
must appear in the GROUP BY clause or be used in an aggregate function
I have a table that looks like this caller 'makerar'
cname
wmname
avg
canada
zoro
2.0000000000000000
spain
luffy
1.00000000000000000000
spain
usopp
5.0000000000000000
And I want to select the ...
- 5,039
469
votes
25
answers
386k
views
Is there any difference between GROUP BY and DISTINCT?
The following two queries each give the same result:
SELECT column FROM table GROUP BY column
SELECT DISTINCT column FROM table
Is there anything different in the way these commands are processed, or ...
- 19.6k
392
votes
6
answers
237k
views
What does SQL clause "GROUP BY 1" mean?
Someone sent me a SQL query where the GROUP BY clause consisted of the statement: GROUP BY 1.
This must be a typo right? No column is given the alias 1. What could this mean? Am I right to assume ...
- 21.9k
374
votes
4
answers
1.1m
views
Reason for Column is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause [duplicate]
I got an error -
Column 'Employee.EmpID' is invalid in the select list because it is
not contained in either an aggregate function or the GROUP BY clause.
select loc.LocationID, emp.EmpID
from ...
- 5,853
324
votes
16
answers
262k
views
What's faster, SELECT DISTINCT or GROUP BY in MySQL?
If I have a table
CREATE TABLE users (
id int(10) unsigned NOT NULL auto_increment,
name varchar(255) NOT NULL,
profession varchar(255) NOT NULL,
employer varchar(255) NOT NULL,
PRIMARY KEY ...
- 25.1k
302
votes
7
answers
561k
views
Is it possible to GROUP BY multiple columns using MySQL?
Is it possible to GROUP BY more than one column in a MySQL SELECT query? For example:
GROUP BY fV.tier_id AND 'f.form_template_id'
- 3,049
296
votes
11
answers
877k
views
How to use count and group by at the same select statement
I have an SQL SELECT query that also uses a GROUP BY,
I want to count all the records after the GROUP BY clause filtered the resultset.
Is there any way to do this directly with SQL? For example, if I ...
- 5,910
275
votes
5
answers
961k
views
JOIN two SELECT statement results
Is it possible to join the results of 2 sql SELECT statements in one statement?
I have a database of tasks where each record is a separate task, with deadlines (and a PALT, which is just an INT of ...
- 3,109
255
votes
4
answers
983k
views
SQL query for finding records where count > 1
I have a table named PAYMENT. Within this table I have a user ID, an account number, a ZIP code and a date. I would like to find all records for all users that have more than one payment per day with ...
- 32.9k
236
votes
18
answers
469k
views
How can I group time by hour or by 10 minutes?
Like when I do
SELECT [Date]
FROM [FRIIB].[dbo].[ArchiveAnalog]
GROUP BY [Date]
How can I specify the group period? I'm using MS SQL 2008.
I've tried this, both with % 10 and / 10.
SELECT MIN([...
- 33.2k
219
votes
6
answers
525k
views
GROUP BY with MAX(DATE) [duplicate]
I'm trying to list the latest destination (MAX departure time) for each train in a table, for example:
Train Dest Time
1 HK 10:00
1 SH 12:00
1 SZ 14:...
- 2,251
206
votes
12
answers
257k
views
SQL - using alias in Group By
Just curious about SQL syntax. So if I have
SELECT
itemName as ItemName,
substring(itemName, 1,1) as FirstLetter,
Count(itemName)
FROM table1
GROUP BY itemName, FirstLetter
This would be ...
- 13.8k
182
votes
9
answers
291k
views
SQL query to group by day
I want to list all sales, and group the sum by day.
Sales (saleID INT, amount INT, created DATETIME)
NOTE: I am using SQL Server 2005.
- 102k
147
votes
14
answers
198k
views
Group by month and year in MySQL
Given a table with a timestamp on each row, how would you format the query to fit into this specific json object format.
I am trying to organize a json object into years / months.
json to base the ...
- 22.1k
140
votes
2
answers
292k
views
GROUP BY to combine/concat a column [duplicate]
I have a table as follow:
ID User Activity PageURL
1 Me act1 ab
2 Me act1 cd
3 You act2 xy
4 You act2 st
I want to group by User and ...
- 2,449
128
votes
12
answers
221k
views
Grouping into interval of 5 minutes within a time range
I have some difficulties with mySQL commands that I want to do.
SELECT a.timestamp, name, count(b.name)
FROM time a, id b
WHERE a.user = b.user
AND a.id = b.id
AND b.name = 'John'
AND a....
- 1,281
126
votes
5
answers
182k
views
SQL to Entity Framework Count Group-By
I need to translate this SQL statement to a Linq-Entity query...
SELECT name, count(name) FROM people
GROUP by name
- 1,525
126
votes
12
answers
360k
views
Select multiple columns from a table, but group by one
The table name is "OrderDetails" and columns are given below:
OrderDetailID || ProductID || ProductName || OrderQuantity
I'm trying to select multiple columns and Group By ProductID while having SUM ...
- 1,423
120
votes
6
answers
273k
views
MySQL "Group By" and "Order By"
I want to be able to select a bunch of rows from a table of e-mails and group them by the from sender. My query looks like this:
SELECT
`timestamp`, `fromEmail`, `subject`
FROM `incomingEmails` ...
- 6,650
114
votes
5
answers
57k
views
Does the order of columns matter in a group by clause?
If I have two columns, one with very high cardinality and one with very low cardinality (unique # of values), does it matter in which order I group by?
Here's an example:
select
dimensionName, ...
- 38.4k
110
votes
5
answers
152k
views
SQL Query to get column values that correspond with MAX value of another column?
Ok, this is my query:
SELECT
video_category,
video_url,
video_date,
video_title,
short_description,
MAX(video_id)
FROM
videos
GROUP BY
video_category
When it pulls the data, I get ...
- 2,176
100
votes
10
answers
236k
views
Group by minimum value in one field while selecting distinct rows
Here's what I'm trying to do. Let's say I have this table t:
key_id | id | record_date | other_cols
1 | 18 | 2011-04-03 | x
2 | 18 | 2012-05-19 | y
3 | 18 | 2012-08-09 | z
4 | ...
- 1,003
96
votes
10
answers
271k
views
GROUP BY without aggregate function
I am trying to understand GROUP BY (new to oracle dbms) without aggregate function.
How does it operate?
Here is what i have tried.
EMP table on which i will run my SQL.
SELECT ename , sal
FROM emp
...
- 1,216
93
votes
1
answer
165k
views
Huge performance difference when using GROUP BY vs DISTINCT
I am performing some tests on a HSQLDB server with a table containing 500 000 entries. The table has no indices. There are 5000 distinct business keys. I need a list of them.
Naturally I started with ...
- 4,866
90
votes
6
answers
127k
views
GROUP BY - do not group NULL
I'm trying to figure out a way to return results by using the group by function.
GROUP BY is working as expected, but my question is: Is it possible to have a group by ignoring the NULL field. So that ...
- 5,121
82
votes
3
answers
71k
views
Pandas equivalent of GROUP BY HAVING in SQL
What is the most efficient way to use groupby and in parallel apply a filter in pandas?
Basically I am asking for the equivalent in SQL of
select *
...
group by col_name
having condition
I think ...
- 4,679
80
votes
5
answers
154k
views
Distinct pair of values SQL
Consider
create table pairs ( number a, number b )
Where the data is
1,1
1,1
1,1
2,4
2,4
3,2
3,2
5,1
Etc.
What query gives me the distinct values the number column b has So I can see
1,1
5,1
...
- 198k
79
votes
2
answers
91k
views
PostgreSQL MAX and GROUP BY
I have a table with id, year and count.
I want to get the MAX(count) for each id and keep the year when it happens, so I make this query:
SELECT id, year, MAX(count)
FROM table
GROUP BY id;
...
77
votes
3
answers
97k
views
How to return a incremental group number per group in SQL
I would like create a data query in SQL to incrementally number groups of rows, grouped on a common datetime and keep the "group numbers" incrementing on the next datetime and so on. These "group ...
- 816
75
votes
14
answers
70k
views
How do I return my records grouped by NULL and NOT NULL?
I have a table that has a processed_timestamp column -- if a record has been processed then that field contains the datetime it was processed, otherwise it is null.
I want to write a query that ...
- 14.3k
71
votes
3
answers
175k
views
Group by with union mysql select query
(SELECT COUNT(motorbike.`owner_id`) as count,owner.`name`,transport.`type` FROM transport,owner,motorbike WHERE transport.type='motobike'
AND owner.`owner_id`=motorbike.`owner_id`
AND transport.`...
- 713
70
votes
5
answers
321k
views
SQL GROUP BY CASE statement with aggregate function
I have a column that looks something like this:
CASE
WHEN col1 > col2 THEN SUM(col3*col4)
ELSE 0
END AS some_product
And I would like to put it in my GROUP BY clause, but this seems to ...
- 6,563
69
votes
4
answers
121k
views
SQL COUNT* GROUP BY bigger than,
I want to select the distinct keys with the occurence number, this query seems functionate:
SELECT ItemMetaData.KEY, ItemMetaData.VALUE, count(*)
FROM ItemMetaData
GROUP BY ItemMetaData.KEY
ORDER ...
- 4,799
69
votes
3
answers
126k
views
SQL Server Update Group by
I'm trying to execute this on MS-SQL but returns me an error just at the Group by line
update #temp
Set Dos=Count(1)
From Temp_Table2010 s
where Id=s.Total and s.total in (Select Id from #temp)
group ...
- 1,180
68
votes
2
answers
68k
views
Conditional SQL count
What is the best way to create columns which count the number of occurrences of data in a table? The table needs to be grouped by one column?
My database is PostgreSQL.
I have seen:
SELECT
sum(...
user3542327
66
votes
5
answers
70k
views
PostgreSQL query to count/group by day and display days with no data
I need to create a PostgreSQL query that returns
a day
the number of objects found for that day
It's important that every single day appear in the results, even if no objects were found on that day. ...
- 2,121
66
votes
6
answers
135k
views
Do all columns in a SELECT list have to appear in a GROUP BY clause
My lecturer stated:
All column names in SELECT list must appear in GROUP BY clause unless name is used only in an aggregate function.
I'm just wanting some confirmation of this as I cannot think of ...
- 12.4k
66
votes
13
answers
98k
views
How to get list of values in GROUP_BY clause?
If I have data like this in a table
id data
-- ----
1 1
1 2
1 3
2 4
2 5
3 6
3 4
How do I get results like this in a query (on sybase server)?
id data
-- ----
1 1, 2, ...
- 5,886
64
votes
5
answers
429k
views
GROUP BY + CASE statement
I have a working query that is grouping data by hardware model and a result, but the problem is there are many "results". I have tried to reduce that down to "if result = 0 then keep as 0, else set ...
- 844
64
votes
8
answers
312k
views
Using ORDER BY and GROUP BY together
My table looks like this (and I'm using MySQL):
m_id | v_id | timestamp
------------------------
6 | 1 | 1333635317
34 | 1 | 1333635323
34 | 1 | 1333635336
6 | 1 | 1333635343
6 ...
- 3,267
61
votes
3
answers
50k
views
How to include BIT type column in SELECT part with out including it on the GROUP BY in T-SQL?
Here is my T-SQL query
SELECT
ProductID,
VendorID,
ProductName= MAX(ProductName),
VendorName = MAX(VendorName),
IsActive = MAX(IsActive) # This brings error
FROM ...
- 50.6k
61
votes
4
answers
82k
views
GROUP BY but get all values from other column
I''ll explain what I need to do on example. First of all, we have a simple table like this one, named table:
id | name
===+=====
1 | foo
1 | bar
1 | foobar
2 | foo
2 | bar
2 | foobar
Now the ...
- 1,479
57
votes
4
answers
163k
views
mysql count group by having
I have this table:
Movies (ID, Genre)
A movie can have multiple genres, so an ID is not specific to a genre, it is a many to many relationship. I want a query to find the total number of movies ...
- 2,633
57
votes
3
answers
79k
views
PostgreSQL WHERE count condition
I have following query in PostgreSQL:
SELECT
COUNT(a.log_id) AS overall_count
FROM
"Log" as a,
"License" as b
WHERE
a.license_id=7
AND
a.license_id=b.license_id
AND
b....
- 633
56
votes
5
answers
110k
views
MySQL Group By and Sum total value of other column
I have 2 columns like this:
word
amount
dog
1
dog
5
elephant
2
I want to sum the amounts, to get the result
word
amount
dog
6
elephant
2
What I have tried so far (and failed) is this:
SELECT ...
- 1,057