Questions tagged [group-by]

GROUP BY : SQL Aggregation for Common Values

Filter by
Sorted by
Tagged with
-1
votes
0answers
14 views

Latest record query

I have table: CI_PREM_CHAR, please refer "Sample Table Records" sheet Actually I have tried few approaches but it is not working: Refer "My Working" sheet I am looking for the output to be as in "...
0
votes
1answer
24 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
15 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
29 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
12 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
26 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
27 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
22 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
29 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
44 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
vote
0answers
61 views

Slow MySql with left join and Group by

Below is the Mysql 5.7 Table query which has a left join with the group by. When I execute it takes more execution time. line_items have around 35 Lakhs (3.5M) data and users have 1000 rows. Below is ...
-1
votes
1answer
22 views

Dividing two column values based on another column value

I have a query that returns the following structure: start_date | pid | uid | type | total 2019-11-10 | 2006933595591018006 | 1812803885757105697 | recommended |...
-1
votes
2answers
31 views

SQL: Select column in group by based on another column's aggrigate

Say I have the table of the following format UID | A | B ---------------- 1 2 1 2 5 1 3 4 1 4 3 2 5 8 2 6 7 2 7 4 3 8 1 ...
2
votes
0answers
25 views

MySQL: Group result set by n groups

How do I group a result set from a query into n groups? In my particular case, I want to spit the result into 10 groups so I can do some SUM and AVG on each group. For example, if I ran this query: ...
0
votes
1answer
34 views

Invalid Use of Group Function Using Join

SELECT a.aircraftid 'Aircraft ID', COUNT(s.serviceid) FROM aircraft a JOIN service s ON a.aircraftid = s.serviceid JOIN( SELECT ( COUNT(ser.serviceid) / COUNT(...
1
vote
1answer
29 views

SQL Server Dynamic Group By

I want to create a de-dupe (remove duplicates) records (actually Guest Profiles) from a table (TableA). The user can opt for 5 criteria (Surname, Name, Email, PostCode,Phone). This means it can create ...
2
votes
1answer
30 views

Add Gameweek Fixeture based on Datefield

having the following table i need to add a new Column called "Round" to represent Round 1, Round 2 and so on, based on the Date Column in the table. Same Round should be given to matches happening in ...
1
vote
1answer
41 views

Get multiple columns from a function depending on outer group by

I have first a selection of trip_id (for a train or a bus e.g.) in a (sub-)Table A trip_id 350 351 360 and want to use a self-written function which takes the used trip_id and gives back the a ...
0
votes
0answers
40 views

Using GROUP BY together with wildcards for table columns

Issue Is possible insert substitution for all cols of joined table? For example like this. select distinct p.product_id, i.filename, pl.* -- instead of pl.price_tax, pl.price_rec, etc. from ...
0
votes
1answer
23 views

Get 2 counts in single query

I need to get count of 2 columns with group by This is my sample data I need to get count of persons from particular state and count of persons from particular country My output should be like I ...
2
votes
2answers
71 views

Select random database records between subset of records having the same count(*) in a query

I have this simple query: SELECT `value`, count(*) as `noHits` FROM `topic` WHERE identifier IN ('" . implode("','", array_keys($processedIdentifier)) ."') GROUP BY `value` ...
1
vote
1answer
25 views

Postgres: Getting the “partition number” instead of row_number

Figure a table with the following data, how to exclude 'alone' values? id; data (1, 'foo'), (2, 'foo'), (3, 'foo'), (4, 'bar'), (5, 'bar'); (6, 'jak'); I try with this: select id, data, row_number()...
0
votes
1answer
124 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
1answer
45 views

More efficient group by to count items in group

My query is: SELECT COUNT("EventType"."id") AS "eventCount", "EventType"."id" AS "EventType.id" FROM "events" AS "Event" INNER JOIN "event_types" AS "EventType" ON "Event"."eventTypeId" = "...
0
votes
0answers
30 views

Collect a column as CSV in GroupBy

I have this schema: Result Story: BILL_INFO has all bill headers. There is a business process called Deposit that occurs once a day: BILL_INFO are updated with a DEPOSIT_ID from DEPOSIT_INFO table ...
0
votes
1answer
42 views

GROUP BY after ORDER BY/LIMIT/OFFSEt?

I have two tables events and eventdate, where eventdate has a row event that corresponds to the id column in events. (So an event can have multiple dates). ` Now I am trying to get the unique events ...
0
votes
1answer
22 views

Group by unique items in an array column and count records that contain each item

I have a table that looks like this: records ------------------------------------- id items[] ------------------------------------- 1 {toaster,piano} 2 {car} 3 {car, toaster} 4 {tv}...
0
votes
2answers
50 views

Group by arbitary monthly time period

I want to group the following data by an user defined period: +------------+--------+ | DATE | Amount | +------------+--------+ | 2019-03-12 | 300 | | 2019-03-15 | 1500 | | 2019-03-25 | ...
0
votes
1answer
56 views

Help needed with SQL Server query: Accessing columns not in GROUP BY clause

I have a table whose columns are as follows: CREATE TABLE Tests ( TestDate date, EntityID int) Using a stored procedure, I want to find the ‘academic year’ of pairs of tests that happened in the ...
0
votes
1answer
130 views

MySQL Simple pivot table with MAX(), GROUP BY and only_full_group_by

I'm having trouble using GROUP BY with only_full_group_by. Here is my employee_job table: +----+-------------+--------+------------+ | id | employee_id | job_id | created_at | +----+-------------+---...
2
votes
1answer
39 views

How to use grouping sets in this query that shows database sizes - adding the last time it was updated

I have the following query that shows me the database sizes, including log dna data files. SELECT [Database Name] = DB_NAME(s.database_id), [Type] = CASE WHEN s.Type_Desc = 'ROWS' THEN 'Data ...
0
votes
1answer
48 views

How to left join on groups of data

I would like to know how to left join columns for each group of data that is return in a set of data. The template needs to fill in the blanks for each group where there are missing records. Groups 1 ...
0
votes
1answer
21 views

How to group time intervals by order of magnitude in Postgres?

I have a bunch of time intervals that I would like to analyze. (Thousands of them.) They range from 0 seconds up to several thousand days, but most of them are less than ten minutes. I would like ...
1
vote
2answers
126 views

How to Return Zero Values In Group Query

I have this query: SELECT fksiteID, SUM(SearchTypePerson) + SUM(SearchTypeLocker) + SUM(SearchTypeSpotRandom) + SUM(SearchTypePersVehicle) + SUM(...
0
votes
1answer
40 views

Aggregating data in .net SQL

I'm converting a database originally written in MS Access VBA to .net and many of the SQL statements that worked in Access don't work in .net. My data set is standard stock market daily information ...
0
votes
1answer
45 views

Sum between multiple joined tables

I have these tables: DROP TABLE IF EXISTS books; CREATE TABLE `books` ( `bookId` mediumint(8) UNSIGNED NOT NULL, `title` varchar(10) NOT NULL, ) ENGINE=InnoDB DEFAULT CHARSET=utf8; ALTER TABLE `...
1
vote
1answer
294 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'...
1
vote
2answers
39 views

SQL queries to get the duplicate records based on trim

I have a table that has got the below set of records. UNMASK MASK 123 897609 00123 896509 0000456 5632789 456 32567889 5678 5632789 From the above table I need to select only the below records....
0
votes
1answer
20 views

Summing Column values based on group by criteria

So I have this query select *, TIMEDIFF(tbltimetracker.tt_endtime, tbltimetracker.tt_begintime) as hours from tbltimetracker LEFT JOIN tblcustomers on tbltimetracker.customer_id = ...
2
votes
2answers
127 views

SQL Preserve the Order in group by

I have records like below in my table. I want distinct records and when I do group by it loses the Order. I want to maintain the order. I have written below query to get the desired however its not ...
0
votes
2answers
84 views

How to group by week even when the count is 0

My below example works fine, the only challenge i am facing is that weeks with 0 results do not show. Here is a sample of my code: My current output: week | amount 4 | 354 6 | ...
0
votes
1answer
38 views

Another - ERROR: column “da2.dependency_device_name” must appear in the GROUP BY clause or be used in an aggregate function

I am attempting to aggregate dependency_device_name into one string, "Dependency List". Here is the error: ERROR: column "da2.dependency_device_name" must appear in the GROUP BY clause or be ...
1
vote
2answers
380 views

sqlite Json grouping json_each

I want to generate json grouping users by year. I have got a "user" table in sqlite with the following columns: date name surname value I would like to create a JSON with the following structure: ...
0
votes
0answers
41 views

SQL Server group by date, time between status

I have a table that looks like the following: id_usr dt_event Sequence 0000001 2015-08-01 14:57:21.737 1 0000001 2015-08-06 15:34:21.920 2 0000001 ...
0
votes
1answer
133 views

Is MySQL syntax GROUP BY … ASC/DESC removed or not?

I would like to ask if MySQL syntax GROUP BY ... ASC/DESC was removed or not? By this worklog task it should be removed but it seems that it is not truth. There are no details in which version it was ...
0
votes
1answer
266 views

Getting total number of payments and percentage

I have a payments table with the following fields: id (int) status (successful, failed, refunded) amount (int) pdate (Y-m-d format) I need to write a query to get, for each day how many payments ...
1
vote
0answers
42 views

MySQL Query Optimization Group By with Max [closed]

I have posted same question on at stackoverflow.com but I did not get any structural solution so I am trying to get answer on dba specific community. Trying to optimise MySQL query which uses Group ...
0
votes
1answer
287 views

Calculate Count Distinct without grouped columns in Oracle

I am trying to extract a few records and I ran into the "not a single group" error. This error can be resolved by removing the (count(distinct dc.DUCT_FID)) as NO_OF_WAYS column expression. However, I ...
0
votes
1answer
40 views

Mysql Performance Issue with Group By/Order By

I am using Mysql 5.7 I have table which having 7006500 rows. My query performing group by and fetching row which has maximum count with each group on column which is already indexed but still takes ...