Questions tagged [group-by]
GROUP BY : SQL Aggregation for Common Values
512
questions
2
votes
1answer
25 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
25 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
17 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
14 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
41 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
54 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
34 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
47 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
16 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
115 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
39 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
141 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
36 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
19 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
77 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
81 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
31 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
170 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
49 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
70 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
41 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
113 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
39 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 ...
0
votes
2answers
50 views
How to optimize SQL query which involves GROUP_CONCAT function with ORDER BY
I wrote the following SQL query to group results and then sort results in each group by date:
SELECT
origin,
provider,
SUBSTRING_INDEX(GROUP_CONCAT(price
ORDER BY departure_date
...
2
votes
2answers
191 views
First order by importance then group by section
In this table scheme http://sqlfiddle.com/#!9/101a9c/9/0
id section rev importance
2 1 b 6
1 1 a 5
3 2 c 9
5 3 e 6
4 ...
0
votes
1answer
37 views
Last value in two levels of group by in MySQL
I have the following data set:
2018-01-02 Oranges 1479 1480
2018-01-03 Oranges 1478 1504
2018-01-04 Oranges 1507 1494
2018-01-05 Oranges 1510 1551
2018-01-02 Apples 2150 2196
...
2
votes
3answers
72 views
Identify proper index for group and order by date and hour (2 columns)
PostgreSQL 10.4
I have a table that has already an index on date column. Current query plan is doing a Bitmap Heap Scan using the existing date index. I would like to add a new index for this query, ...
0
votes
0answers
42 views
MySQL how to GROUP SUM based on JOIN TABLE
I have Three Table, One Master Table and Other Two Table have relation with it.
Below is the Three Table:
1. ball_by_ball table, this table holds full information by ball by ball:
CREATE TABLE `...
0
votes
2answers
36 views
For states (col) receiving exactly X number of items (row), sort the items by weight (col)
I have been trying to figure this out for a few hours now but haven't gotten it. Each row in the table is a package.
I am able to get the States with the exact number of packages, but don't know how ...
0
votes
1answer
83 views
Group json data in db using a query
I have a db whose values are as follows:img Can I call the json value of listing_id(23,27....so on) in properties column(with having row value as "listing") using a single query, I would Really ...
0
votes
1answer
44 views
Complex join 3 tables with sum and group by
I'm trying to generate a report for one of our departments that pulls data from 3 tables. The basic structure is below:
CREATE TABLE `evd_us_county_mio_2018q4` (
`state_code` INT(11) NULL DEFAULT ...
1
vote
1answer
27 views
Count Assets Barcodes and Group by Rooms
I have 2 tables Asset Register and LocationRoom. I want to count the number of asset barcodes in each room.
I want the output to be the list of Asset Barcodes, Description,RoomNo and count for ...
1
vote
1answer
41 views
How to correctly use group by for getting the record with the closest time for each rec_id
Background
I have a table records which stores rec_id, rec_time and rec_value. A record is stored for each rec_id only when its rec_value changes. I would like to query the value for a bunch of ...
1
vote
1answer
24 views
How to count more than one time with different conditions?
Having a table like this:
I want to get a result like this:
Something like:
SELECT ParentId, COUNT(SomeValue = 1) [CountMin], COUNT(SomeValue > 1) [CountRest]
FROM MyTable
GROUP BY ParentId
0
votes
1answer
93 views
Limit groups of results based on a column value
I'm not sure I formulated the question properly, but anyway.
I have a table with level-2 comments, some of which, expectedly, have the same comment_parent column value. How do I make a SELECT query ...
0
votes
1answer
240 views
Why do I receive the error message “'<column name>' must appear in the GROUP BY clause or be used in an aggregate function”?
Where am I going wrong? Why am I getting that message when I add the MAX function?
The query involved:
WITH
references_cve AS (
SELECT vulnerability_id, reference
FROM ...
0
votes
0answers
34 views
“GROUP BY” to keep only the first 2 rows based on date
I have a list of online robots conversation, with the time indicating when a client started a conversation and when the robot answered.
I am trying to measure how long does the robot take to provide ...
1
vote
3answers
183 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 ...
2
votes
2answers
353 views
how to count distinct value per day with start time and end time
my title may seem a bit confusing but here's the context.
I have a table 1. It contains the reference code per connection to SSID with value of start time. Now I have a scenario wherein the user ...
2
votes
2answers
542 views
Sum up counts in subquery
I am writing a query to check if I have got duplicates in a certain column.
SELECT COUNT(thecol) FROM thetable WHERE thecol IS NOT NULL GROUP BY thecol
HAVING COUNT(*) > 1
That will give me an ...
0
votes
1answer
75 views
How to use case and filter with a group by statement and having an aggregate calculation?
I would like to do the following
SELECT
PO.col1,
PO.col2,
CASE
WHEN PO.col3 <> 8 THEN PO.col4
WHEN PO.col3 = 8
THEN CASE
WHEN (ROUND(CAST(...
0
votes
2answers
35 views
sum only different id's values with any aggreagte function in postgresql
I need your help. Let's assume that we have a table like below
+---------------+-------+--------+----------+
| barcode | shop | id | entrance |
+---------------+-------+--------+----------+...
0
votes
1answer
363 views
Using MATCH, AGAINST multiple times in MySQL fulltext search
I'm using MySQL database for my e-commerce project.
And here is my query for search:
SELECT DISTINCT p.id, p.name, p.price, p.created_at,
MATCH(p.name) AGAINST('Sony* xperia* FHD* ' IN BOOLEAN ...
0
votes
2answers
173 views
Duplicate result when I select with distinct values from foreign key constarint
I want to select values from two different tables which have one to many relationship between them.
During select it's retrieving duplicate values even when I use distinct and group by. Above table is ...
1
vote
2answers
78 views
How to group same columns from same two tables, but from two different result sets and/or conditions
I am having troubles coming up with a query for something that should be quite simple.
Initially, I had two tables of data that I generated joint result sets from; an events table, and a groups table ...
0
votes
2answers
86 views
WHERE clause on a TIMESTAMP column causes serious performance penalty
I have a pages and a page_views tables with 100k~ & 4M~ records respectively.
I run a query to get the top pages that have the most page_views:
select `pages`.`id`, `pages`.`host`, `pages`.`...
1
vote
1answer
60 views
Order after rollup - oracle 11g
I am using rollup() function in group by expression to show the result query,
but my output like this:
Year Month AmountOfMoney
==== ===== =============
1998 March 130....
