Questions tagged [group-by]
GROUP BY : SQL Aggregation for Common Values
536
questions
-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 ...
