Questions tagged [group-by]
GROUP BY : SQL Aggregation for Common Values
0
votes
2answers
13 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
33 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
votes
0answers
13 views
Group by getting correspondent next column
I'm facing an issue which I cannot solve and would require some assistance. Having a table as follows:
date ts isactive val description
"31-Dec" "2018-12-31"...
1
vote
2answers
31 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
50 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
31 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
424 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
26 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
77 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
25 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
23 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
29 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
34 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
45 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
32 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
43 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
24 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
46 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
62 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
33 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
33 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
54 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
37 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
81 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
119 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
57 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
46 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
109 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')...
0
votes
0answers
170 views
MySQL COUNT in GROUP BY with LEFT JOIN table
Query I've tried:
SELECT UPPER(section.name) AS SECTION, COUNT(employee) AS 'EMPLOYEE COUNT', SUM(salary.amount) AS SALARY
FROM employee
LEFT JOIN section ON section.id = employee.section
LEFT ...
0
votes
1answer
45 views
Select only those records that have same multiple values for a particular column if it's existing?
Below is an example of my pricing_supports table have more than 3 milions rows.
There some rows having same values in the first_origin_id, delivery_id fields.
If there're records with same ...
0
votes
1answer
38 views
Selecting a group from groups based on one row condition
There is a group of rows which have the losscode and service odom. The contractnumber and service odometer are used to group the rows.
Please, may you help me to find the way to select only the row ...
1
vote
1answer
70 views
Find missing timestamps grouped by key: finding gaps in my data
I have a table that has a timestamp, some data and a identifying key for the data source:
create table test_data (
id serial primary key,
key text,
timestamp timestamp with time zone
)...
0
votes
1answer
109 views
SQL query to convert Column to Row using Pivot/Unpivot [duplicate]
My Select Table. I tried Pivot query to get my expected result but I was struct to generate the expected result.
WITH PersonOptions(ID,[Year], [Text], Value1, Value2) AS
(
SELECT 'A',...
0
votes
2answers
52 views
GROUP BY clause / Aggregate function error [closed]
I am having a problem creating a Table View:
DROP VIEW IF EXISTS product_variation_stock_view;
CREATE VIEW product_variation_stock_view AS
SELECT
product_variations.product_id AS ...
-2
votes
1answer
43 views
How to group a few row values together to put into a new table? [closed]
I have a table:
+-----------+-------+
| risk | count |
+-----------+-------+
| high | 5 |
| low | 30 |
| medium | 81 |
| very high | 2 |
| very low | 72 |
+------...
0
votes
1answer
55 views
Get percentage of group by
I have this data:
CREATE TABLE tickets(user_id int NOT NULL);
INSERT INTO tickets VALUES (1);
INSERT INTO tickets VALUES (2);
INSERT INTO tickets VALUES (3); -- 3 times
INSERT INTO tickets VALUES (4);...
-1
votes
1answer
22 views
How to group rows
I don't understand how to retrieve the results from the following tables depending on some prerequisites.
I have 2 tables
Table "project"
| id | category_id | count_view
| -- | ------------| ------...
0
votes
4answers
116 views
Can Group By and Count on MySQL be optimized?
I have a 100 million row table on MySQL. I need to count rows for certain ranges and I have the proper indices for filtering rows. Let's say a SELECT statement returns 20000 results, but all I need is ...
0
votes
4answers
34 views
filter out the records with same id having different values in a column
Can anyone help me to solution for below issue.
create table [order1] (order_id int, CartTransaction_Id int, Status_Id int);
insert into [order1] values
(357488,87214,1),
(357489,87214,8),
(357490,...
1
vote
2answers
34 views
Join tables with some same values
I'm trying to get lines that have same ADAPTERID and Attribute_Name, but Attribute_Value can be active or designed automatically.
I used:
SELECT * FROM database1
where Attribute_Value IN ('ACTIVE','...
0
votes
2answers
52 views
Calculate ticket sales by using SUM in SELECT clause
In this ticketing setup on dbfiddle, I am trying to calculate the ticket sales for a particular event by multiplying the ticket price and the total number of tickets sold for that event, based on ...
0
votes
0answers
26 views
Ordering group by when it's a math equation
I'm using MariaDB's system versioned tables and when querying the version history, I'd like to group the versions by x number of seconds (in my case, 300 - or 5 minute intervals).
The problem I'm ...
0
votes
1answer
221 views
oracle sql: How to not select select duplicate records from table
Can any help me to fix the below sql query
SELECT DISTINCT ORDER_NUMBER, FLAG
FROM TABLE
Below was the result
Order_number FLAG
LP-13288 false
LP-13288 true
LP-13292 false
LP-13290 ...
1
vote
3answers
89 views
Return a value only when conditions satisfy for entire column
Can anyone help me to how to achieve below result
Input data
ORDER_NUMBER LINE_ITEM FLAG
LP-13288 1 TRUE
LP-13288 2 FALSE
LP-13288 3 (null)
LP-13288 4 FALSE
LP-13289 1 ...
0
votes
1answer
159 views
Calculations with group-wise sum and row count
Is it possible to sort a table by a value, divided by the sum of values within each year, again divided by number of rows for each year? I've tried to illustrate it by this faulty pseudo-query:
...
0
votes
2answers
103 views
MySQL Very Slow Performance Using Order by Clause
I have a one table with millions of entry.Below is table structure.
CREATE TABLE `useractivity` (
`id` bigint(20) NOT NULL AUTO_INCREMENT,
`userid` bigint(20) NOT NULL,
`likes` bigint(20) DEFAULT ...
0
votes
1answer
57 views
SQL group by multiple columns
Say I have the following table, where a group of 3 people from 3 locations work together on a project that is scored.
| | Score | Possible_Score | Loc_A | Loc_B | Loc_C |
|---|-------|--------------...
