Questions tagged [group-by]
GROUP BY : SQL Aggregation for Common Values
0
votes
0answers
6 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
13 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
25 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
57 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
36 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
28 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
17 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
35 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
22 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
24 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
votes
0answers
18 views
I need to pivot the the input table in to this format using oracle
Input format of the table:
Needs to be converted into this format:
Using a PIVOT.
1
vote
1answer
21 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
30 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 ...
-1
votes
1answer
70 views
SPROC Gives error [closed]
Running this sproc on oracle gives error
Error report -
ORA-00937: not a single-group group function
ORA-06512: at "TEST_CON", line 4
ORA-06512: at line 1
00937. 00000 - "not a single-group group ...
0
votes
1answer
95 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
32 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
83 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
225 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
48 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
24 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
15 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
73 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 ...
1
vote
2answers
41 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
53 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
34 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....
11
votes
1answer
432 views
Why is this stream aggregate necessary?
Check out this query. It's pretty simple (see the end of the post for table and index definitions, and a repro script):
SELECT MAX(Revision)
FROM dbo.TheOneders
WHERE Id = 1 AND 1 = (SELECT 1);
...
0
votes
1answer
30 views
Slow query with multiple group by
I have a query to get all the comments from threads where a given user has commented and not yet seen the comment. So if user 1 commented to a topic, then user 2 commented after his comment, then user ...
2
votes
1answer
80 views
Filter on value in another row
Please see image of the data and my code so far.
In this example I only want the first two rows to be retrieved. The reason for this is because where JobID is 1684 the sum of STILL_REQUIRED is > 0
...
0
votes
1answer
27 views
I have 2 queries with only one difference that I want to merge in just one query
I have two queries in MySQL that are basically the same, where the only difference between them is a min() or max(), I would like to know if it is feasible to join the two.
I believe that the ...
0
votes
1answer
24 views
How to generate missing values for each employee for each day
CREATE TABLE `EmpWorkCount` (
`emp_Id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`day` int(10) unsigned NOT NUL,
`created_At` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
`...
1
vote
2answers
32 views
count for missing values in group by
We have a user table :
CREATE TABLE `User` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`name` varchar(60) NOT NULL,
`created_At` datetime NOT NULL DEFAULT ...
1
vote
2answers
41 views
How to get average of lat/long from cities by county that they're in
I found this script but I'd like to get the average of the latitudes/longitudes so I can just have the latitude/longitude of the counties. I tried
SELECT id, CITY, COUNTY, LONGITUDE, LATITUDE, AVG(...
0
votes
1answer
60 views
Enforce known functional dependency in a group by clause
Let's consider I have the following tables:
CREATE TABLE users(
id serial PRIMARY KEY,
age integer
)
CREATE TABLE products(
id serial PRIMARY KEY,
sku ...
2
votes
0answers
60 views
How to write group by and having in a relational algebra interpreter?
I am practising writing relational algebra (RA) expressions in RA interpreter from Duke, but I can't figure out how to represent group by or having (from SQL). I have this expression, but I need to ...
0
votes
1answer
47 views
MS SQL rolling grouped sum for every new data entry
I am using MS SQL 2016, and I have a table which looks something like that (simplified)
Object Date Amount Owner TypeOwner
O1 20180101 10 A X
O2 ...
0
votes
1answer
26 views
MYSQL: How to select all attributes of all duplicate entries?
I have a database that containes user data and need to dig up only the entries where two attributes are duplicate between users (surname and social security number). I need all of the attributes for ...
1
vote
0answers
48 views
Select Rows within a Group based on a condition
CREATE TEMPORARY TABLE TempSlotUpdated (
slotId INT(10) NULL,
slotstarttime datetime NULL,
slotendtime datetime NULL,
totalTime time NULL);
CREATE TABLE `Apt` (
`...
2
votes
1answer
67 views
Build a histogram of road speed based on road length by grouping road segments
A data set look like this:
The table doesn't have sum(segLength yet), but can be computed by summing up sum(segLength) from road_table group by roadID
A roadID (e.i. Lake Street) belongs to a ...
1
vote
1answer
39 views
SQL query with GROUPING SETS to get subtotals with a HAVING clause
Let's say I have a (fictional) table City with the following columns:
Country: string
State: string
City: string
Population: integer
I want a query that shows the total population by State, by ...
0
votes
1answer
35 views
how to optimise this group by on sys.dm_exec_query_stats?
I would like to know the last 1008 queries run in my systems and with this intent I have put this query together:
-- get the last 1008 queries run
SELECT top 1008
execquery.last_execution_time AS [...
0
votes
3answers
64 views
Select only records that have different values for a particular column
In my table there are some records that have different name values for the same account number and I want to extract only the records that have different values in the name field, but that share the ...
0
votes
2answers
39 views
Count all products (with qty > 0) group by all brands. But get aslo count zero for brand which have 0 products
I have table:
brands
brand_id name
1 Apple
2 Sony
3 Nokia
4 HTC
products
id brand_id qty
1 1 3
2 3 0
4 1 1
2 2 1
I need to ...
5
votes
1answer
129 views
If at least a negative number, sum; otherwise just show
I have a need to sum values together only if there is a negative value present in a partition. If there are no negative values in a partition, it should just output the row.
Here's what I have right ...
2
votes
1answer
71 views
Change level(s) of totals with GROUP BY
I am interested in the totals and the details of every line, in other words, the results of the second query.
Is there an easier way to do a rollup, and just have grandtotals?
(my 'live' query has ...
0
votes
1answer
309 views
Select unique records from multiple records based on max date
I have table like below
+----+----------+-----+-------------+--------+
| ID | ADDR | IND | update_Date | Sq_NBR |
+----+----------+-----+-------------+--------+
| 1 | PHYSICAL | Y | 11-Oct ...
-1
votes
2answers
84 views
Group by in subquery
So my problem is the following. Consider this query:
SELECT
P.id,
(SELECT barcode FROM product WHERE publisher_id = P.id LIMIT 1) AS barcode_sample
FROM
publisher P
WHERE
P.name LIKE '...
1
vote
0answers
15 views
Get contiguous date ranges in grouped data [duplicate]
CREATE TABLE `rings` (
`ID_RingType` char(2) default NULL,
`Number` date default NULL,
`ID_User` int(11) default NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
INSERT INTO `rings` (`ID_RingType`, `...
0
votes
1answer
221 views
How to UPDATE from JOIN with GROUP BY
In a SELECT query of
SELECT b.id, MIN(IFNULL(a.views,0)) AS counted
FROM table1 a JOIN table2 b ON a.id=b.id GROUP BY id
HAVING counted>0
How can I turn this query to UPDATE as
UPDATE b.number ...
-3
votes
1answer
481 views
T-SQL: how to use SUM() with GROUP BY
I have a very basic question:
I have this code which works and returns me 23 rows:
select
Issue_ID
,sum(HoursSpent) as TimeSpent
--,DateClosed
from SixMonthsReview
where StaffAssigned = 'Teddy ...
0
votes
2answers
36 views
Select Sum from two joined tables
There are structures:
CREATE TABLE `invoices` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`date` date NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB;
INSERT INTO `invoices` VALUES (1,'2018-09-22')...
