All Questions
46 questions
2
votes
1
answer
115
views
How can I get all results concatenated from a column group by UID?
I need to build a SQL query, for MySQL 5.7, if it is possible, to "GROUP BY" uid and title and concat the permissions column and separate with a |? I am saying GROUP BY but I do not know if ...
- 1,888
1
vote
1
answer
119
views
Select first row of a particular group with one attribute selected as 0 while all other rows should be selected as is
I have a requirement where I need to select first row of a particular group and for all other groups I need to select the row as is in the same query.
Here is my table
CREATE TABLE mytable(
afid ...
- 197
-1
votes
1
answer
1k
views
how to select date time step by 60 second from first value to last value in table
I want to add 60 seconds to the date value from the first date and return the data if it exists in that interval
like this
datetime
value
2021-05-02 12:20:1
5
2021-05-02 12:20:15
8
2021-05-02 12:...
1
vote
2
answers
4k
views
SQL - count rows with same value
I would like to group and list all the rows that have the same value (gruppo_muscolare.nome) and count how many values (esercizio.nome) are related to the same value, but if I use GROUP BY then I only ...
- 113
0
votes
2
answers
52
views
How do I name the thing that is done in the FROM part of the query?
So, I have recently started studying data bases in Uni, MySQL.
I am practicing some queries with different stuff that the professor gave us.
One of the questions i have found is as follows:
Show the ...
- 3
0
votes
3
answers
172
views
find groups with duplicate versions
I want to find all the groups in a table that have duplicate group version.
A group can have multiple group versions.
Each group version can have multiple members.
A group 'version' is defined by ...
- 533
1
vote
2
answers
2k
views
How to use GROUP_CONCAT for a column and COUNT(*)?
I get the ids of a query by GROUP_CONCAT.
SELECT GROUP_CONCAT(ArticleID) FROM TagMap WHERE Tag IN(...)
How can I merge this query with a COUNT(*) group by
SELECT ArticleID,COUNT(*) FROM TagMap WHERE ...
- 4,531
0
votes
1
answer
51
views
How to COUNT co-tags from a tag_map table in mysql?
I have a simple tag_map table
CREATE TABLE TagMap
(
TagID mediumint(7) unsigned,
ArticleID int(11) unsigned,
FOREIGN KEY(TagID) REFERENCES Tags(TagID) ON DELETE CASCADE,
FOREIGN KEY(ArticleID) ...
- 4,531
0
votes
1
answer
36
views
SELECT with a GROUP BY column?
The situation is like this:
I have a table that has one row for each copy of a letter, which also contains the letter ID. I want a single query that outputs each letter copy row with the letter copy ...
- 103
1
vote
2
answers
616
views
How to get sum of 3 sub count queries and group them using an attribute
I have 3 queries as follows:
For the date 2019-12-02, I am calculating 3 type of values. The total value for 2019-12-02 must be the sum of these 3 queries.
Q1
SELECT count(*)
FROM ...
0
votes
2
answers
50
views
For states (col) receiving exactly X number of items (row), sort the items by weight (col)
I have been trying to figure this out for a few hours now but haven't gotten it. Each row in the table is a package.
I am able to get the States with the exact number of packages, but don't know how ...
0
votes
1
answer
6k
views
Limit groups of results based on a column value
I'm not sure I formulated the question properly, but anyway.
I have a table with level-2 comments, some of which, expectedly, have the same comment_parent column value. How do I make a SELECT query ...
- 103
2
votes
1
answer
100
views
Filter on value in another row
Please see image of the data and my code so far.
In this example I only want the first two rows to be retrieved. The reason for this is because where JobID is 1684 the sum of STILL_REQUIRED is > 0
...
- 21
0
votes
3
answers
2k
views
Select only records that have different values for a particular column
In my table there are some records that have different name values for the same account number and I want to extract only the records that have different values in the name field, but that share the ...
- 3
0
votes
1
answer
315
views
MSSQL select count entities in date range
I have a task to find how often a user performs actions per a minute.
I have found the solution with grouping by date part:
CREATE TABLE UserAction(Id int, Date datetime, Action nvarchar(200))
Go
...
- 103
1
vote
2
answers
50
views
Road lifecycles: Partition rows into groups
I have a constr_events table:
create table constr_events
(
event_id number(4) not null unique,
road_id number(4) not null,
constr_date date not null,
reset_condition number(9,2)...
- 1,497
2
votes
1
answer
28k
views
MySQL group by multiple columns on multiple tables only one result per group
I have three different tables and I need to count how many rows has a specific value (COUNT(track)) then group by tracks.id and sort by only one result per user (tracks.uid).
My three tables:
`users`...
- 173
1
vote
1
answer
63
views
Is GROUP BY select-list-ref less optimized than GROUP BY colname?
Just wondering if GROUP BY 1 is slower than GROUP BY col?
SELECT x, count(x)
FROM foo
GROUP BY 1;
- 64.7k
10
votes
2
answers
22k
views
Why can I select all fields when grouping by primary key but not when grouping by another column
How is this a valid statement (where id is the primary key of the table):
select * from table group by id ;
and this is not:
select * from table group by name ;
ERROR: column "pgluser.id" must ...
- 829
5
votes
2
answers
730
views
SQL Server: Order of Rows
We all know that a simple statement such as:
SELECT * FROM stuff;
should not produce an ordered result. However, when I try to demonstrate this, it always comes out in primary key order.
Moreover a ...
- 2,919
3
votes
2
answers
256
views
T-SQL Count in single select
I have a table called Catalog. It has multiple columns, for example CreatedByID and ModifiedByID that contain user IDs. I would like to get count of how many documents a user has created and edited.
...
- 43
0
votes
1
answer
124
views
Group COUNT by month less than variable
In SQL Server 2008 R2, I have the following two tables, Employee and EmployeeStatus:
CREATE TABLE [dbo].[Employee]
(
[employeeID] [int] IDENTITY(9500,1) NOT NULL,
[firstName] [nvarchar](50) ...
- 123
1
vote
1
answer
51
views
ORACLE: Changing the grouping of a data to be based on month breakdowns
To keep this short and to the point, I am looking at modifying the grouping for this SQL query to go from NAME to Month/yy
Here is a example of the script, and screenshots of the output file
This is ...
- 1,042
3
votes
1
answer
13k
views
How to get number of active users with one SQL-Query?
I am running a site similar to craigslist where users can create classifieds and write/reply messages. In order to get an idea of activity I would like to get the amount of active users per month.
...
- 313
2
votes
1
answer
878
views
Unknown column 'actions_in_group' in 'field list'
I keep getting: Unknown column 'actions_in_group' in 'field list'
Here's the SQLFiddle: http://sqlfiddle.com/#!9/4ddf0/19
SELECT *,
SUM(actions_in_group) AS total_rows_in_group,
GROUP_CONCAT(...
- 117
0
votes
1
answer
103
views
Update rows in a table with an index column starting from 0 with consecutive numbers
I have a few tables in a Oracle db with some "rule": The INDEX_COLUMN must start from 0 and must be consecutive numbers and the column NRCRT must start from 1 and also consecutive numbers. I've made a ...
- 349
1
vote
0
answers
109
views
Find the last record of group of overlapped records
I have a little dilemma with filtering out result.
The result and what I want to achieve is not very trivial and I didn't find an answer anywhere after extensive search. So here is my problem:
I ...
0
votes
2
answers
754
views
sql problem with multiple linked tables
I have an Oracle db and the following tables:
raportor:
id id_pct_lucru
58382 327342
ses_rap
id an status
1 2012 I
21 2013 I
41 2014 D
ch_trat
id fk_id_raportor ...
- 349
0
votes
1
answer
58
views
Group-by in an Oracle database
on an Oracle database. If I make an:
select id,id_operator,denumire,cod_fiscal,loc_den,str
from rap where is_punct_lucru=1
and id_operator in (
select id_operator from rap
where ...
- 349
0
votes
2
answers
733
views
MYSQL Select Distinct with SUM
I am developing a Stocks management application, the Portfolio register table has 4 columns, other than date and registry_key, and it looks like this:
----------------------------------------
| ...
- 111
1
vote
1
answer
1k
views
select group by with multiple AND conditions on the same columns
I've an sql table like this:
SID|SERVICE_SID|ATTRIBUTE_SID|VALUE
1 2 5 "VAL1"
2 2 4 "VAL8"
3 2 3 "VAL4"
4 11 5 ...
- 113
4
votes
1
answer
21k
views
How to speed up slow query using GROUP BY and CASE?
I'm not very experienced with MySQL and and trying to figure out how to speed up a slow query using GROUP BY and CASE statements.
CASE 1 -- GROUP BY and CASE
SELECT SQL_NO_CACHE m.id, m.sku, m....
- 201
2
votes
1
answer
3k
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), ...
- 121
3
votes
1
answer
16k
views
Group by age range and gender with aggregation function on some columns
In Mysql, I have a patient table with id, birth_year and gender. Next table is transactions in which I have id, patient_id [FK from patient] and checkup_date. I need to select rows such that I get ...
- 133
0
votes
1
answer
1k
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 ...
- 167
0
votes
1
answer
424
views
Is there any performance difference between a group by statement and nested select statements in sql?
I am curious to know about the differences between using group by statement and nested selects to achieve the same result.
for example
consider I have two tables such as the following:
tblPurchase
...
- 157
2
votes
1
answer
3k
views
Select query - group by and sub group by
CREATE TABLE [dbo].[temptable]
(
[Id] INT NOT NULL PRIMARY KEY IDENTITY,
[uid] INT NOT NULL,
[pid] INT NOT NULL,
[event] INT NOT NULL,
[timeStamp] DateTime NOT NULL
)
I want ...
- 123
0
votes
3
answers
179
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
1-...
- 55
1
vote
1
answer
87
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 ...
- 25
0
votes
1
answer
4k
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 ...
- 55
0
votes
1
answer
2k
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 ...
- 4,531
5
votes
1
answer
64k
views
How to get MAX and MIN in GROUP BY query?
In this 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 col1.
How to select MAX and MIN for each value of col1.
...
- 4,531
8
votes
1
answer
46k
views
Error "column does not exist" in a SELECT with JOIN and GROUP BY query
I'm using PostgreSQL 9.1 with a Ruby on Rails application.
I'm trying to list the last version of each "charge" (in my history table : hist_version_charges) belonging to the same project id (...
- 183
8
votes
1
answer
50k
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 ...
- 4,531
4
votes
1
answer
1k
views
SELECT multiple sensor values in one query
Background
I have a couple of devices, each with a couple of sensors. I log these every now and then and stores them in a table described below. When someone requests a web page, I fetch a couple of ...
- 529
3
votes
2
answers
11k
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(*)
...
- 4,531