Questions tagged [group-by]

GROUP BY : SQL Aggregation for Common Values

Filter by
Sorted by
Tagged with
-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. ...

1
2 3 4 5
12