Questions tagged [group-by]
GROUP BY : SQL Aggregation for Common Values
574
questions
-5
votes
1answer
39 views
Need help with the following SQL query [duplicate]
I need help with the following output. Tried case, Distinct, Group By, failed. Join might help, but still couldn't write a query with join.
0
votes
1answer
19 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) ...
1
vote
1answer
46 views
SQL Trying to sum and group by distinct values
I have a table
+-------+----------+------------+----------+---------+
| Plant | LineName | WorkCenter | Material | ProdQty |
+-------+----------+------------+----------+---------+
| x | xl |...
0
votes
1answer
19 views
Count percentage of column with GROUP BY
Consider the following query:
SELECT
country
, count(id) AS "count"
FROM
lead
WHERE
date_part('year', lead.since) = date_part('year', CURRENT_DATE)
GROUP BY "country"
ORDER BY "count" DESC
;
...
7
votes
1answer
210 views
GROUPING SETS returning unexpected results with calculated expression
Here we have two similar queries using grouping sets
where the SELECT clause contains some expressions calculated in aggregation:
SELECT RN10, RN10 / 10, COUNT(*) FROM
(
SELECT RN, RN/10 AS ...
0
votes
1answer
53 views
SQL count(*) grouped by date range
I have a table as follows:
DROP TABLE IF EXISTS #PersonnelRecord;
CREATE TABLE #PersonnelRecord
(
person_id int NOT NULL,
company_id int NOT NULL,
entry_date date NOT NULL,
exit_date ...
0
votes
0answers
18 views
Using rank in postgresql
With this query in postgresql
WITH ExtractedPosition AS (
SELECT P.rider_id,
P.start_date,
P.day_index,
P.start_hour,
P.end_hour,
extract(...
0
votes
1answer
48 views
Except command doesn't work in SQL [closed]
I am trying to make an except in SQL but it doesnt exclude the data even though they are identical.
SELECT A.payment_id,
SUM(A.importe_trx) monto_total
FROM [dbo].[ABONOS]...
1
vote
1answer
15 views
Return previous running total when value is null in a time series
This is a continuation of a previous question, where table definition and sample data can be found. (Huge thanks to @Erwin Brandstetter for the help there).
All of this is being done on a PostgreSQL ...
0
votes
1answer
45 views
Indexing a GROUP BY Select with Multiple Inequalities
I have a pair of linked issues I need help with:
Issue #1: How to Index a Table to Optimize for a "Group By" SELECT
I have a table of stock price information with the following Create statement:
...
1
vote
1answer
34 views
Generate multiple running totals with GROUP BY day
I have a set of transactions of stock purchases by users and I want to keep track of a running balance of each stock as the year progresses. I am using a windowing function to track the running ...
0
votes
1answer
24 views
Aggregating data by group and time gaps
I've got a postgresql+postgis database filled with GPS data:
CREATE TABLE "my_table" (
"id" serial NOT NULL PRIMARY KEY,
"gps_id" text NOT NULL,
"lat" numeric NOT NULL,
"lon" ...
0
votes
1answer
13 views
How to enforce strict handling of GROUP BY with non-aggregate / bare columns or UPDATE with multiple-row SELECT in SQLite
Unlike some other RDBMS, SQLite does allow for bare columns in SELECT querys with GROUP BY, i.e. not all selected columns need to be part of the group by or aggregate functions otherwise. In this case,...
0
votes
1answer
10 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 ...
0
votes
1answer
45 views
Using case and group by in select statement not working
I'm using a case and group by in a select statement and I would like for the case field name (item number) to not be apart of the group by clause statement. I've calculated the invoice weight as well ...
-1
votes
1answer
26 views
Using session variables to generate GROUP information
We want to know whether the given approach is correct or not. I understand that there might other and presumably better and more optimized ways of accomplishing the task. But it is a curiosity that I ...
0
votes
0answers
23 views
MAX with group by by 2 columns
I have a query
create table film_actor (
actor_id integer,
film_id integer);
create table film (
film_id integer,
title varchar);
create table actor (
actor_id integer,
...
0
votes
1answer
15 views
MySQL : count occurences on different columns in a same query
I've got his table:
+----+------+---------+
| id | user | upgrade |
+----+------+---------+
| 0 | A | NULL |
+----+------+---------+
| 1 | B | NULL |
+----+------+---------+
| 2 | ...
1
vote
1answer
41 views
Avoid Sort on Indexed Group By caused by DATE_TRUNC
Is there a way to avoid Sort operation for GROUP BY, caused by the DATE_TRUNC function not being recognized as tstamp, which is a part of the ix index? That function doesn't really affect the order ...
0
votes
1answer
14 views
How to create month ranges without using a specific date in Postgresql
What's the best way to create month ranges without using specific dates?
For example, today is April the 4th and I want to calculate the sales rate of the past 12 months, that I can run every month ...
0
votes
1answer
69 views
(mySQL Aggregate function, JOINS, and GROUP BY) sakila database project
The current pandemic is making it very difficult to contact my teachers for assistance, so I'm hoping I can describe my problem well enough to try getting some help here.
The database used is the ...
0
votes
1answer
32 views
Understanding the use of bitwise operators in MySQL?
Can someone explain the purpose of using bitwise operators(like BIT_OR) in MySQL queries. For example, if have a table such as following:
What is the purpose of aggregate operation like:
SELECT name,...
0
votes
1answer
23 views
postgresql cumulative counts in date range
I'm trying to get the cumulative count of rows (by group_id) between two dates that represent a time period where the row was active.
I have a table like this:
group_id | id | type | start_date ...
3
votes
2answers
80 views
MySQL: Using DISTINCT and GROUP BY together?
I saw the following MySQL query that that uses both DISTINCT and GROUP BY together:
SELECT DISTINCT user_id, post_id, post_content
FROM some_table
GROUP BY post_id, user_id
HAVING ...
-1
votes
2answers
75 views
How to get data from database week wise between two given dates?
I have a SQL database where i saved data of all dates in table. What i want is, If given any two dates, i want all the data between those two dates in weekly format where week starts on Monday. if ...
0
votes
0answers
19 views
How can I have multiple group by in MongoDB aggregation?
My sample data is:
[{
"company": "apple",
"last_view": "2020-01-01"
}, {
"company": "google",
"last_view": "2020-01-01"
}, {
"company": "google",
"last_view": "2020-01-01"
}]
Now to ...
0
votes
1answer
111 views
Select distinct values in column with condition
I have the products table as follows:
ProductID | ItemId | Color
---------------------------
1 X 5N
2 X 10N
3 X 2N
4 Y 12S
and a ...
0
votes
3answers
54 views
How to group rows while filtering out values per each group?
Imagine you have the following setup:
CREATE TABLE namechanges (
id text,
new_name text,
change_date timestamp
);
INSERT INTO namechanges VALUES
('x', 'alice', '2020-03-01'),
('y', 'Bob T.'...
0
votes
1answer
32 views
Group by with unrelated counts
I have three tables, one, two and three.
one
two
three
With this query
select one.id, count(two.id) as num_twos, count(three.id) as num_threes from one
left join two on one.id = two.one_id
left ...
0
votes
2answers
18 views
Combining previously different rows after changing value during query
I'm currently running a query that is supposed to return a salesperson's aggregated revenue, grouped by individual clients.
Because of how user input was handled, there are a lot of near-duplicate ...
0
votes
1answer
23 views
Select images where ALL images are greater than pixel width
I will try to explain this clearly.
I have a table of 200,000 records that stores pixel sizes for our product images. A variety of image sizes have been saved over 20 years, and products do not ...
0
votes
1answer
33 views
SQL: Merge rows when value doesnt change in continuous time
I am new to SQL and I need to summarize a table that has this information: Serial Number, StartDate, EndDate, StatusID and TimeInState, what I need is to merge rows that keep the same StatusId during ...
0
votes
0answers
13 views
GROUPING SETS equivalent in MDX?
I'm creating an SSRS report that allows the user to select the different type of aggregations that are displayed in the report (e.g. overall, by type, by discipline, by type/discipline).
Currently ...
0
votes
1answer
18 views
Detect with Left Join duplicates entries and return the first value, also return sum of duplicates entries
¡Hi!, i have a case with two tables than can have a lot of matches entries and the time of fetching increases a lot.
This tables can be
Table A: Employees
-------------------------
| Name | ID | ...
0
votes
1answer
62 views
Slow query when matching columns from second table in JOIN with GROUP BY
Update: so, as mentioned in the comments thread, it seems the ORDER BY might be the most direct cause of slow execution. So, the question should be: how can this query use ORDER BY score and execute ...
1
vote
0answers
46 views
Properties of grouping in relational algebra
The properties of relational algebra (commutativity, associativity, distribution) allow us to take a relational algebra expression and transform/rewrite it into another one which is logically ...
1
vote
1answer
52 views
Postgresql group by for many columns
I have such query:
SELECT "Movie"."title",
"Movie"."id",
"Movie"."publicationDate",
"Movie"."authorID",
"Author"."photoURL" as "authorPhoto",
concat("Author"."...
0
votes
3answers
46 views
Postgres fetch exact value when satisfied all conditions
Here are the table structure and sample data.
CREATE TABLE public.product (
id serial NOT NULL,
opid int4 NULL,
opvalue int4 NULL,
info ...
0
votes
1answer
30 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 ...
0
votes
0answers
39 views
How to compress a query to get distinct COUNT and SUM aggregate information on joined rows with sub-queries in Microsoft SQL
I have a query that counts some incidents that happen based on joining some groups of companies and regions.
SELECT RC.RegionId, RC.CompanyId, CC.Name, Quantity = SUM(CASE WHEN
II....
0
votes
1answer
28 views
Trouble producing a query that groups a selection of groups
In terms of context, I believe that all that is needed is to know that I have a table, t_sale, that logs sales made. It logs a few things, such as day and location of sale in the shop, but what is ...
-3
votes
2answers
22 views
Select query for group amounts per month and year (MySql) [closed]
I have a table name "transactionshistory" with columns
and I would like to create a select query where a SUM the amounts per month and year
for example
Total_Amount | MONTH | YEAR
100 | ...
0
votes
1answer
38 views
Distinct on two columns, how to rid from 'duplicates'
I have a table with messages, that have 'msg_from' and 'msg_to' fields.
when I want to select all current chats and last message from each of them I use the following query:
select distinct on (...
0
votes
1answer
18 views
PostgreSQL equivalent query which has order by clause alone
Please help me to get equivalent PostgreSQL query for the below oracle query
SELECT sum(COLUMN1),sum(COLUMN2)
FROM Table_name
ORDER BY COLUMN3;
0
votes
1answer
145 views
Exclude rows with unique values in one column from grouped mysql query
The following table represents interactions form users with different projects:
id user_id project_id
17373 6336 1001
17374 6336 1001
17375 6336 1001
...
-2
votes
1answer
34 views
What alternative sql method can I use to see more records, GROUP BY id only shows one row per unique id
What I want is per map id I want to be able to see the top highest scoring gangs (also I should be able to say from which date or a specific single day), (i thought of using GROUP BY ID, but it would ...
0
votes
2answers
31 views
Get Closest Value With GROUP BY
I'm having issues with getting the closest result depending on the data that will be given by the user. The user gives me the Length, Width, and Height of a box then I compute the volume and compare ...
1
vote
1answer
26 views
Select rows within group before condition is met
I want to select rows within each group following a condition in time. I have the following table trajectory_data in PostgreSQL 10 with daily points for each group.
| traj_id | timestamp | height |...
1
vote
2answers
35 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
1answer
52 views
Union from Multiple Tables in many databases and group results in batches of n
First post in dba, so please be gentle.
I have a project I am working through and have 4+ SQL Server databases to work with.
These DBs contain 2 (or more tables) I wish to UNION the results from.
...
