All Questions
31 questions
1
vote
2
answers
93
views
How do I optimize this query that gets the newest entry of each group?
I have a query, that finds the newest item of each group based on the ID returned by a subquery. The problem is, that the table has a bit over 5000 entries and even with only this amount of entries ...
- 13
1
vote
1
answer
885
views
How to GROUP BY boolean when im already grouping by time frames 8hr/11hr/14hr/17hr/20hr in MySQL
I need to group 2 tables (Key = analysis_id <=> id) where my output should show the last week or month or year, grouped by a Time frame and grouped by bolean "R" or "L". I ...
- 29
0
votes
1
answer
377
views
How to group by week/month/year and time frame 8hr/11hr/14hr/17hr/20hr in MySQL
I need to group 2 tables (Key = analysis_id <=> id) where my output should show the last week or month or year, grouped by a Time range. I have around 4000 inputs from users.
Example of tables ...
- 29
-2
votes
1
answer
24
views
Extracting data from two tables
I have( for example) a table called A and a table called B.
Table A contains tons of user data( age, country bla bla) and is connected to B by forein_key=id( B only has ID/NAME.
I want to find out how ...
- 1
2
votes
2
answers
267
views
SELECT groups by individual members AND sum other values
Using MySqL 5.7.34 and having a table, groups_members, with 4 columns
ID
group
member
organization
1
A
Bob
Apple
2
A
Susan
Apple
3
A
Joe
Apple
4
B
Steve
Microsoft
5
B
Mike
Microsoft
6
B
Bob
...
- 123
0
votes
0
answers
202
views
MySQL Creating Unnecessary Sort Index When an Aggregate Query is a Subquery
I have a complicated aggregate query, using the functions SUM and GROUP_CONCAT, that starts returning results in a reasonable amount of time by itself. But when this query is wrapped in another query, ...
0
votes
1
answer
2k
views
SELECTing n rows for each GROUP with WHERE condition
I SELECT n rows for each ArticleID from TagMap table
SELECT a.ArticleID,a.TagID FROM TagMap a
LEFT JOIN TagMap b ON a.ArticleID = b.ArticleID AND a.TagID < b.TagID
GROUP BY a.ArticleID,a.TagID
...
- 4,531
0
votes
2
answers
90
views
How to UPDATE faster with GROUP BY?
I have a typical article and tag_map tables,
CREATE TABLE `articles` (
`ID` int(11) unsigned NOT NULL,
`Tags` tinyint(3) unsigned DEFAULT NULL,
`Title` varchar(512) DEFAULT NULL,
PRIMARY KEY (`...
- 4,531
0
votes
1
answer
815
views
Sql Group by on subquery rows
I have this table :
ArretProductionJournee(Id, DateArret,HeureDebut,HeureFin,EnumArret)
Example :
DateArret ||HeureDebut ||HeureFin ||EnumArret
2020-11-30 ||2020-11-...
0
votes
1
answer
783
views
Using alias in Group by
I am running into a problem where I have to use an alias (Ordernum) in a group by. I have found that this could be done somehow with subquery but I don't have any success with that and also those are ...
- 1
0
votes
1
answer
2k
views
PostgreSQL error: "subquery uses ungrouped column"
I have a query with subqueries that use only elements from the GROUP BY clause:
SELECT
DATE(cm.created_at) as "date",
COUNT(1) as total_message,
room_id,
(SELECT ...
- 111
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 ...
1
vote
1
answer
4k
views
Sub Query with group by returns more than 1 row Issue
I am facing the issue of subquery returns more than 1 row cause I am using group by in the subquery and outer query both to get the desired result.
This is my query
SELECT Count(*) ...
2
votes
1
answer
2k
views
Having a subquery somehow do the same group by as the outer query
i'm pretty decent with SQL but it's not my main experience of expertise. A colleague asked me a stumper of a question the other day. I was wondering if you guys could provide assistance.
Basically we'...
- 121
1
vote
3
answers
4k
views
How to apply outer limit offset and filters in the subquery to avoid grouping over the complete table used in subquery in Postgresql
I have legacy tables similar to the following:
employee
------------------------------------
| employee_id | name
------------------------------------
| 1 | David
| 2 ...
- 111
-1
votes
2
answers
19k
views
Group by in subquery
So my problem is the following. Consider this query:
SELECT
P.id,
(SELECT barcode FROM product WHERE publisher_id = P.id LIMIT 1) AS barcode_sample
FROM
publisher P
WHERE
P.name LIKE '...
- 3
0
votes
4
answers
4k
views
filter out the records with same id having different values in a column
Can anyone help me to solution for below issue.
create table [order1] (order_id int, CartTransaction_Id int, Status_Id int);
insert into [order1] values
(357488,87214,1),
(357489,87214,8),
(357490,...
- 129
1
vote
2
answers
1k
views
How to use GROUP BY in an efficient way without losing attributes? [closed]
I am working with PostgreSQL.
My idea is to group players who appear more than 3 times in table-a.
table-a = <player_id, year_world, c, d, e>
With the first subquery I get player_id and the ...
- 109
2
votes
1
answer
3k
views
Calculate quantity of tickets based on how many reserved using sub queries
I'm designing a database that will hold ticketing information. The 3 tables I use to track ticketing are
tbl_ticket_type to the types of events an event can have
tbl_ticket_count to keep track of ...
- 325
2
votes
1
answer
13k
views
Filtering subquery results with group by
I have a join that works but seems hacky because it orders the subquery and uses group by to filter everything but the top subquery result. Without doing so, the cartesian product of s and ...
- 23
0
votes
1
answer
5k
views
Joining subqueries with GROUP is slow
Can anyone help me with the following query?
SELECT
voc.username AS Medewerker,
voc.bestvastonbep_count AS Doorgezet,
IFNULL(cc.call_count, 0) AS Telefoongesprekken,
voc.bestvastonbep_count / ...
0
votes
2
answers
1k
views
Full outer join in MySql
I'm on MySQL trying to get the user's daily post count grouped by post category.
So for every day in a given date range, with no gap, I need the post count for each existing post category for a given ...
- 135
1
vote
1
answer
4k
views
Sub-Query group-by count(*) not returning 0
Prefix: I am working on an application that is tracking employee training time and I need to get the total number of employees that have reached 75 total hours of training. After researching for some ...
- 99
0
votes
1
answer
54
views
Mysql query to get count result and group by
I've got three tables with different records for users,
User
username | realname | date
evn-az-3ju john 11/2012 03:09:40 p.m.
jwyvm_rdyt steve 12/2012 ...
- 101
0
votes
1
answer
163
views
Check for existing matches to find the field their grouped-by
So I have a table of values associated with different sessions using a field called sessionid, lets say the table has 3 other fields: itemcode, itemcost, & itemeffect.
What I end up doing, is ...
- 101
0
votes
1
answer
2k
views
Is there a way to used grouped data multiple times in a query
Suppose a table contains employee id, department name and salary. I want to select the department that gives total salary greater than average salary given by all departments.
The solution that I ...
- 3
2
votes
2
answers
36k
views
Multiple COUNT fields, GROUP BY
I'm trying to group two columns in my MySQL database and show counts for them respectively (as separate columns). Is there a way of doing this or do I have to join two sub-queries?
I'm aware of WITH ...
3
votes
1
answer
3k
views
MySQL Update a table based on the last data from another table while grouping with conditions
I need a query to UPDATE a series of fields based on another table
Table lp_plates_backup
| uid | brand | model |
| 1 | | |
| a | Old | Error |
|...
- 45
33
votes
1
answer
178k
views
Postgres error [column must appear in the GROUP BY clause or be used in an aggregate function] when sub query is used
I have two tables employee and phones. An employee can have 0 to n phone numbers. I want to list the employee names with their phone numbers. I am using the below query which runs fine.
SELECT ...
- 433
0
votes
1
answer
495
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 name ...
- 153
1
vote
2
answers
156
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 ...
- 153