1,686 questions
0
votes
2
answers
121
views
Filter Rows Within Each Group
I have a table t containing minute-level data with fields such as datetime (timestamp), stock_id (stock code), and ret (return). My goal is to:
First, group by date(datetime) and stock_id, and ...
- 11
0
votes
2
answers
123
views
Using GROUP BY - check for multiple conditions with a WHERE or HAVING clause for a single ID [closed]
I have a table of customer data. I will be joining it to a location table. Customer ID is distinct but Location ID is not because multiple customers can belong to one location. Each customer is ...
0
votes
2
answers
100
views
Return an ID with at least 1 specific status using Group By Having Clause
This is my sample data set in a table called "status": fiddle
create table status(id, status)as values
(1, 'in')
,(1, 'out')
,(1, 'in')
,(1, 'in')
,(2, 'in')
,(2, 'in')
,(...
- 1,175
0
votes
3
answers
73
views
What is the correct way to count a record with two rows with two different values, my having clause is not working
I'm trying to count a book in the database where that belong to different locations.
For example the book "Interview with the Vampire" by Anne Rice exist in both library code of 40 and 30 (...
- 15
2
votes
5
answers
106
views
Group IDs from one table, then count groups having different values in another table
I have the following Postgres SQL tables:
db<>fiddle
create table t1(id,sval)as values
(88, 'X129de')
,(4, 'YHK33e')
,(44, '1K4892')
,(53, 'YHK33e')
,(42, 'YHK33e')
,(...
- 27
0
votes
3
answers
142
views
Aggregate may not appear in WHERE clause
I have this query
SELECT
a.Campaign,
COUNT(DISTINCT a.Customer) AS 'Records',
MIN(a.LastRecord) AS 'First Record',
MAX(a.LastRecord) AS 'Last Record',
MAX(a.daytime) AS 'Records up ...
- 9,887
2
votes
1
answer
110
views
Why can't I just use "HAVING" clause all the time instead of "WHERE"? It works the same basically if you use it before/without "GROUP BY"
If "HAVING" is more versatile than "WHERE" in SQL then why bother with it?
SELECT AVG(salary) AS avg_salary
FROM employees
WHERE salary > 50000;
SELECT salary
FROM employees
...
- 23
-1
votes
3
answers
167
views
R newbie: R equivalent to SAS proc sql, group by, having, count, remerging summary statistics
In SAS i write a code that in the log looks like:
2 proc sql;
3 create table test as
4 select
5 ID,
6 GENDER,
7 BIRTHYEAR
8 from
9 libname.dataset
10 group by ID
11 having ...
3
votes
3
answers
87
views
Check how many rows in a grouping have the MAX value SQL
My dataset looks like this:
TEAM_ID PLAYER_ID NUM_POINTS
21 39 20
21 50 10
21 67 10
22 74 0
22 73 0
I want ...
- 714
-2
votes
1
answer
74
views
Find how many days with 0 steps every user has. HAVING COUNT(x)=0 returns nothing
I'm working with the Kaggle dataset of Fitbit used for a Google data analysis certificate project,
and I'm trying to find how many days with 0 steps every user (id) has.
demo at db<>fiddle
...
0
votes
1
answer
74
views
Group by and having clause
Consider the following relational schema:
candidates (candidate_id, skill)
[Sample input table]
Select candidate_id
from candidates
group by candidate_id
having sum(if(lower(skill)='python' or lower(...
- 1
0
votes
1
answer
60
views
how to find duplicate results in elasticsearch 6
I had an elasticsearch with version 6, now I need to find duplicate documents in a index "es_index" by field "userName" with same field value. That means I need to write query like ...
- 125
1
vote
1
answer
73
views
How select query from record count double in MS Access
I'm trying to select query from row count double based on ID and DATE in MS Access.
I want to keep it as a single query. What are the possibilities of this being applied? Or another method?
Please ...
- 213
-2
votes
3
answers
120
views
Using HAVING without GROUP BY in SQL [duplicate]
I know this will not work but can someone explain why?
SELECT *
FROM employees
HAVING salary = MAX(salary);
The code does not work with WHERE clause because it's filtering against an aggregate ...
- 3
-3
votes
1
answer
58
views
SQL : having after group by returns what? [duplicate]
enter image description here
I wanted to get 1,3,4 back as their tiv_2015 values are appearing more than once, but I got 1 only.
Help in need to understand how select works after having clause.
select ...
0
votes
1
answer
67
views
SQL data wrangling help using the Having statement
The below code (Databricks SQL) produces the table following it. I am trying to adjust this code so that the output only includes zip5 records that have only 1 (or less) of each facility_type ...
- 191
1
vote
2
answers
155
views
Laravel Eloquent - Using Mariadb variables with HAVING
I am having an issue filtering a Mariadb query through HAVING on a computed column based on a Mariadb variable.
I am having an issue in Laravel Elquent where a query I am running is producing no ...
- 11
0
votes
0
answers
40
views
SQL, return user position based on specific ORDER BY using MySQL 5.7 [duplicate]
I have tables
users: (id, name)
scores: (id, user_id, value, created_at)
How can I calculate rank of user by sum of value, but if sum for some users the same upper should be user with highies last ...
-1
votes
2
answers
101
views
Problem with customer name display involving average [closed]
I'm trying to write an SQL query that displays customers' names who have a total purchase greater than the average purchase of all purchases.
I tried using the code below, but it gives an error.
...
-1
votes
2
answers
1k
views
What is meaning of SQL error "Each function argument is an expression, not a query"
I have a table customer_purchases with 3 columns: customer_id, product_id, quantity, where each customer_id can buy multiple products having different quantities.
I need to find customers whose ...
0
votes
2
answers
65
views
SQL - CASE with IN SELECT
I am trying to isolate those records of [table] which only have specific (let's say bad) attributes. Bad attributes are those with ids 3, 6 and/or 7. To do that, I came up with the following solution:
...
- 15.2k
0
votes
0
answers
26
views
How to fill 2 separate columns with only the most recent data associated with data
In a database that I'm creating, I need to match parent contact information with kids that are given to me in a list format. I'm matching student ID numbers with parent emails as primary keys and ...
0
votes
1
answer
73
views
how do I modify my having clause so my query will show the result that I want
So currently my query shows result of all the "preferred" travel destinations of countries based on the continent, and I'm adding a having clause to show the continents that have more than 1 ...
- 15
-5
votes
1
answer
96
views
SQL Sum() function with a condition
How does this work in SQL sum(o.product_name='Bread') > 0,
e.g if the orders table (o) has product_name column such that the entry is Bread then will the condition
o.product_name='Bread' return 1 ...
- 11
0
votes
2
answers
65
views
MySQL join 3 tables and having clause with SUM on activityScore column
I have 3 tables : accounts, contacts, contact_activity. Accounts can have multiple contacts, also multiple activities whereas contacts can also have multiple activities. adding tables queries below:
...
0
votes
0
answers
90
views
Hackerrank, challenges problem. Why does the query return unique output of count (challenges)?
Here is the link for the problem: Hackerrannk-challenges
One of the solution:
SELECT
c.hacker_id, h.name, COUNT(c.challenge_id) AS cnt
FROM
Hackers AS h
JOIN
Challenges AS c ON h....
- 9
-2
votes
2
answers
46
views
using aggregate function with SQL having clause
can anyone tell me, why is this query giving an error
Write a query to find the maximum total earnings (earnings) for all employees as well as the total number of employees (num_employees) who have ...
1
vote
2
answers
86
views
Count the number of records group by remark
How to count the records of each remark? Each remark has a different number inside the remark column value. I need to group the records based on Remark content but not with cid###. need to group by ...
- 69
0
votes
1
answer
352
views
I am confused by SQL select from where in group by having
[![enter image description here][1]][1]now say I have a database, where there is a table kits_parts_needed, and I need to figure out what kit I have parts for, there are fields in this table that ...
- 36
0
votes
0
answers
80
views
SQL 'GROUP BY' & 'Having' problem on stratascratch
The task was - https://platform.stratascratch.com/coding/514-marketing-campaign-success-advanced/official-solution?code_type=1
And I'm a little bit confused in the code.
SELECT user_id
FROM ...
0
votes
3
answers
97
views
delete query inner join having
I made a delete query to delete all records in tbljournaalposten where the field volgr occurs more than once.
delete from tbljournaalposten
where tbljournaalposten.ID in(
SELECT
...
- 21
-2
votes
2
answers
74
views
SQL Server difference between two queries?
This is my table:
enter image description here
I have a problem I don't understand the difference between the following two queries:
Query #1:
SELECT
productID,
SUM(soldQty)
FROM
Sales
...
- 1
0
votes
1
answer
47
views
How can I include all the other fields of table when I'm filtering the data by only 1 field using group by hand having?
So this my query below:
select creditcard
from user where preferred_card = '1'
and uder_identification_code = 'creditcard'
group by creditcard having count (1) > 1;
So I'm trying to get list of ...
- 15
0
votes
1
answer
20
views
MySQL Getting filtered row from one table based on a value in another table
I am having an issue joining these two tables. The query below will return ID-A 1 and 8. However, I also want to check if ID-A has StatusCode= 2 in TableA. How do I join the two tables that return ...
- 2,532
0
votes
1
answer
54
views
Unknow column in having clause - mysql
I know this has been answered, but I can't decipher the solution from the responses given my lack of experience with mysql, so forgive me for asking the same question again. I am converting ms-access ...
- 21
0
votes
2
answers
642
views
Having trouble using MIN() in having clause
Trying to select earliest order_date for each customer_id.
Using following query
SELECT customer_id, order_date
FROM Delivery
GROUP BY customer_id
HAVING MIN(order_date) = order_date
But ...
- 23
0
votes
1
answer
45
views
How to select a group of columns related to another column in MySQL?
I have a MySQL table containing users, files and file ages as shown below.
Users
Files
File age
A
1
42
A
2
17
A
3
4
B
4
85
B
5
73
B
6
11
C
7
6
C
8
9
C
9
3
Now I need to select the names of all files ...
- 12
1
vote
1
answer
96
views
Return multiple rows where count in the Database is more than 1 matching on 2 columns. If BOTH columns match in another row, return both rows
I am querying our IT service management system and want to bring back tickets that match the following criteria.
Must be an incident
Must be in an open state
The user who logged it must have another ...
- 11
-1
votes
1
answer
63
views
How is the having clause creating the correct output in this example?
How the HAVING clause works in SQLite? My code works and returns the name and division of the highest paid person in each division. However, the use of the HAVING clause does not make sense to me ...
0
votes
1
answer
59
views
How do I join multiple tables to get a result set that includes rows that fulfil group by and having clause as well as rows that satisfy one criterion
I have two tables - input:
Input_table
ait_no
schema_nm
column_nm
table_nm
1
aic
ssn
sic_tabl
2
aic
ssn_1
bhue_tab
1
aits
ssn_no
eyfu_tab
1
aits
ssn_number
gic_tab
2
aic
is_snn_no
yfjs_tab
2
aic
...
- 285
0
votes
1
answer
42
views
Trying to count a grouping and only return greater than 1
I'm looking to write a SQL statement but having trouble.
Let's say I have a table with the following data:
id
ip
1
1
1
1
1
1
2
1
2
1
3
2
3
2
4
2
4
2
4
2
5
3
5
3
6
4
6
4
7
4
7
4
I want the results of ...
- 1
1
vote
1
answer
55
views
Django filter on many to many with having count
After a lot of try, I am not able to translate this kind of sql queries into django filtering.
Basically, it is retrieving all users with at least x selected tags (and other filters).
In the case of ...
- 2,983
1
vote
1
answer
215
views
SQL query for items joined to multiple categories
I've got these 2 tables, content item and a join table to categories. I simply need a query to select items that are in multiple categories (in ALL categories, not in any category).
-- content items
...
- 93
0
votes
1
answer
57
views
SQL to find emails that have custID 0 AND custID <> 0
I have an ecommerce database that contains an 'Order' table (tblOrders). In this table there are individual orders with OrderTotal, Name, Email, etc. If the user checks out as 'guest', a CustID of 0 ...
- 357
-2
votes
1
answer
42
views
Aggregation of query in sql
I have table, like:
Id
Account
Date
Amount
1.
ad@ind
07.11.2022
354
2.
ad@ind
10.11.2022
586
I need to find record, where is maximum number of month and get amount in this date. How is it to do? Max(...
- 15
1
vote
0
answers
100
views
Why doesn't the 'HAVING' function work in RdfLib SPARQL queries?
I'm trying to use the HAVING funtion in a SPARQL query. While the query works fine in Protege, it does not return any result when I use it in a query in the rdflib package(it does not raise an error ...
- 23
1
vote
1
answer
3k
views
Show a unique first names from the patients table that only occurs once in the list
Show unique first names from the patients table which only occurs once in the list.
For example, if two or more people are named 'John' in the first_name column then don't include their name in the ...
1
vote
1
answer
40
views
How to see the duplicates in a different columns in Oracle SQL?
I try to find the duplicates but at the same time be able to see it in a different columns but this duplicates is in the same report and also I verified the duplicates by year.
report
id_tip
date
...
- 23
-1
votes
2
answers
49
views
SQL multiple tables count by single field
MySQL / MariaDB structure:
Person
| id |
|----|
| 1 |
| 2 |
| 3 |
Car
| person_id | car |
|-----------|-------|
| 1 | Ford |
| 2 | Mazda |
| 3 | Ford |
| 3 | ...
- 283
0
votes
0
answers
65
views
No data filtering in SQL
Why does the number of records increase when adding a 'country_id' constraint?
My first request. 30 entries are displayed here :
SELECT
app_id,
sum(installs)
FROM
application.app
...
- 15