89 questions
0
votes
2
answers
99
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
1
vote
1
answer
110
views
How can I get the total count and the count with the condition in the same SQL query?
I have a table like this:
id
value
0001
0
002
100
I want to make a table like this:
total_count
fail_count
pass_rate
2
1
0.5
May I know how to write in one Hive SQL query? Thanks.
the pass_rate is ...
- 51
1
vote
1
answer
42
views
I need to get the average of 2 SQL query results in ASP.NET C#
I am trying to fetch some data using the count function in sql into my asp.net project in 3 parts:
Part one will use the 1st SQL query as follows:
select distict, shop_number, count(Emp_id)
from ...
1
vote
2
answers
46
views
MySQL count percentual of occurrences greater than
I have a table like this.
+ day + person + amount +
+------+---------+--------+
+ 1 + John + 4 +
+------+---------+--------+
+ 1 + Sam + 6 +
+------+---------+--------+
+ 2 ...
- 543
0
votes
3
answers
71
views
group by in queries that contains subqueries
How can I group by the following query by customerId
select
(SELECT SUM(Purchase) FROM Customers where type in (17,21)) as 'Purchase'
,(SELECT SUM(Point) FROM Customers) as 'Point'
FROM CUSTOMERS
...
user18472331
1
vote
1
answer
86
views
Counting records based on conditions over potential null values
I have this tables:
CREATE TABLE customer_orders (
"order_id" INTEGER,
"customer_id" INTEGER,
"pizza_id" INTEGER,
"exclusions" VARCHAR(4),
"...
- 115
1
vote
2
answers
117
views
How to return 0 for all time intervals instead of nothing when counting
I have a query for deployment table. There is no data for hotfix column now. I want to show all change count without hotfix and with hotfix for time intervals.
Table data:
deployTime
changeno
hotfix
...
- 179
1
vote
2
answers
87
views
How to get the sum of column in Table2 where table1 meet condition
In my Orders table I have a column Status that holds two items such as 'Paid' or 'Not paid'. In Order Details table I have Amount column. I have written a query to select the number of customer that ...
- 65
0
votes
1
answer
62
views
Is there a way to convert a query contianing multiple subqueries fetching the same column from same table, into one query
Is there a way to convert the following into a query without subqueries (or with a lesser number of subqueries) in order to make it faster.
select ( +(select sum(coalesce(quantity,0)) from ...
- 1,040
0
votes
1
answer
2k
views
Concatenate multiple strings ordered (PIVOT)
I'm trying to concatenate the names of multiple Employees (ordered) based on their hierarchical level, which ranges between 0 and 4, without duplicates. The issue itself is fairly easy to solve (...
- 11
0
votes
2
answers
93
views
SQL: how to get sum of grouped items for a given corhort
I have the following orders table:
(1, 2, '2021-03-05', 15, 'books'),
(1, 13, '2022-03-07', 3, 'music'),
(1, 14, '2022-06-15', 900, 'travel'),
(1, 11, '2021-11-17', 25, 'books'),
(1, 16, '2022-08-03', ...
- 201
0
votes
1
answer
54
views
Shorten queries with multiple joins
Is there a way to shorten this query?
This query returns the result I want but I feel like this is to long. Are there tips to make an efficient query wilth miltiple joins?
SELECT home.team_id, home....
- 13
2
votes
2
answers
103
views
Use multiple INNER JOINS to transpose one column in multiple columns
I have the following table
CREATE TABLE "holes" (
"tournament" INTEGER,
"year" INTEGER,
"course" INTEGER,
"round" INTEGER,
&...
- 406
0
votes
0
answers
28
views
MySQL iterate information from one table in to 2 columns in query
I have a database which holds maintenance reports for unique units that I need to pull information from in a single query. My issue is the convoluted way the information is stored.
SELECT date(reports....
- 105
1
vote
1
answer
58
views
Generating a table of subtraction of two colum count
Hello i want to make a summary table where the filter is the segment and date
The columns will be the subtraction of count of two column in same table so far i have come up to this
select
case
...
- 1,960
1
vote
1
answer
96
views
SQL query output results from one table into two columns
I have a set of data which I have queried and have extracted the right data, but would like the output to be in two different columns. The query is
(
select month(observation_date) as month, count(...
- 2,213
-1
votes
2
answers
83
views
Filter as count on SQL SERVER
Helo!
How I make the following syntax of postgresql in SQL server without create subquery
PGSQL:
SELECT
COUNT(*) AS "QUANTIDADE TOTAL",
COUNT(*) FILTER(WHERE SEXO = 'Masculino') AS &...
1
vote
2
answers
52
views
filter `id` with a specified value in another column and it does not appear more than once on each `id`
I have table user:
user_id status
1010 1
1010 3
1010 3
1011 5
1011 2
1011 3
1012 3
1012 3
i want to filter user_id with the condition that the number "3&...
- 61
0
votes
1
answer
60
views
SQL grouping unique occurrences by date
I am looking for a MySQL query to count how many people visited a web-app. I have a database storing information about visitors, they type and when they tried to login:
+-----+-----------+--------+----...
- 16.8k
-1
votes
2
answers
91
views
Can I get two count results in a query?
I am using MariaDB. I want to have two count results in a query.
The first query is:
SELECT
license_key,
COUNT( * ) AS expired
FROM license_product
WHERE expire_at > NOW()
GROUP BY ...
1
vote
1
answer
71
views
Summarize a column with multiple boolean columns
I have a sqlite database containing a table who looks like this, to store many to many relation :
ID_1
ID_2
TOOL
1
2
x
1
2
y
1
3
x
1
3
z
2
3
x
2
3
y
2
3
z
The two first columns contains ID of pairs of ...
- 107
1
vote
2
answers
533
views
Compare the same id with 2 values in string in one table
I have a table like this:
id
status
grade
123
Overall
A
123
Current
B
234
Overall
B
234
Current
D
345
Overall
C
345
Current
A
May I know how can I display how many ids is fitting with the condition:
...
- 51
1
vote
1
answer
127
views
How to subtract two rows from two different selects?
I'm trying to subtract the total number shares for each symbol from the sell orders and the buy orders so I can have that total of shares owned.
buy = db.execute("SELECT symbol,SUM(shares) as ...
- 13
1
vote
2
answers
234
views
MySQL split row into 2 columns and group by, is that possible?
I got a table with 2 columns app and grupo_id
select count(*) total, app, grupo_id from devices d group by app, grupo_id ;
output:
total
app
grupo-id
7
1
4
2
0
4
5
1
2
1
1
1
3
0
1
so the app I would ...
- 35
1
vote
1
answer
78
views
SQL Count() need 0 as well
I just have one table and i want to count every entry for label where start is 0
right now i try it like this:
SELECT label, COUNT(start) AS Anzahl, user_id FROM datensammlung Where (start='1') AND (...
1
vote
1
answer
75
views
Counts grouped by two columns output into columns & rows
Let's say I've got some data with two columns: name and ice cream purchases.
Joe | Chocolate
Mary | Vanilla
Beth | Rocky Road
Fred | Vanilla
Mary | Rocky Road
Joe | Vanilla
Joe | Chocolate
etc...
...
- 330
1
vote
2
answers
711
views
SQL CASE in GROUP BY
I have a query that is meant to get the count of each reaction on a comment and whether the user is one of the people who made that reaction. However, my case statement returns includesMe:1 for EVERY ...
- 65
0
votes
3
answers
65
views
Oracle sql combine table and do counting
How to count the total project participation (it means the project count) for the project Name Lakers and participate in Client meeting between 12/25-27/2022 for all employee display with the UserAc ...
- 29
0
votes
0
answers
35
views
How to transform a row in a column based on duplicated data? [duplicate]
I have been trying to do this since a while now but haven't been successful so far.
I'm currently trying to translate a row into a single cell of another row in SQL Server.
Example:
I have 4 rows:
ID
...
- 61
1
vote
1
answer
671
views
Use SUM function inside a Pivot table
Below is the SQL I use to create my pivot table. I need to replace null values with '0', however, I get an error when trying to use a case expression inside the pivot. Is this not possible?
SELECT *
...
- 13
0
votes
2
answers
1k
views
SQL table join - 1 column data split to 2 columns depending on another column
I have some data in x2 tables (d and i) that I want to join and split 1 table's data (i) in to 2 columns depending on data in another column of table i.
Example of the data I have is (I have cut out ...
- 105
1
vote
1
answer
133
views
Trouble with SQLite Query/Subquery
I am having trouble with a SQLite query and I've tried googling it over the last few days, but I don't even know what the operation is called to come up with relevant answers.
I have a pretty standard ...
- 3,599
1
vote
2
answers
278
views
Calculating ratios in postgresql
I new to postgresql and I am trying do calculate a rate in a table like this:
class phase
a sold
b stock
c idle
d sold
I want to calculate the total count of sold phases / total ...
- 351
1
vote
1
answer
99
views
How can I count the visits according to the date?
The VISITS table
USER_ID
VISITED_IN
518
2022-04-13 20:37:04
518
2021-12-29 22:26:50
518
2021-03-04 04:22:46
518
2021-08-13 02:14:54
518
2022-05-26 20:49:01
518
2022-05-05 17:47:46
518
2021-09-12 08:58:...
- 1,737
1
vote
1
answer
74
views
Sql query to join query results
I have one query result as:
Wname NewCases
Mon 2
Tues 1
Sat 3
Second query result as:
Wname FollowUp
Tues 3
Wed 4
Friday 2
I need to write mysql query ...
- 23
1
vote
1
answer
154
views
How to normalize column values in new column?
I've below data in table, val1,val2,val3 are of double type. x represents record is having old value and y represents new value. So we need to concatenate all double values in respective col (oldVal,...
- 387
1
vote
1
answer
70
views
TSQL : Count distinct values overall and based on criteria by group
I have
Groupvar
Subvar
Val
G1
A
x
G1
A
x
G1
B
x
G1
B
y
G1
C
z
G1
C
z
G2
A
x
G2
A
x
G2
B
y
G2
B
z
G2
B
w
G2
C
z
and I want
Groupvar
Allcount (distinct Val)
Acount(distinct Valwhere Subvar=A)
Bcount(...
- 85
1
vote
3
answers
58
views
Order By date for multiple date rows
I am trying to write an sql query to combine a rows so the result will look something like this:
post_id
date_1
time_1
date_2
time_2
500
20220303
20:00:00
500
20220202
20:00:00
Here is what Ive tried ...
- 275
2
votes
2
answers
2k
views
GROUP BY and create columns for each unique value grouped on (output as 1 row always), then use the output as a JOIN - SQL Server
I need to GROUP data BY 2 columns (BillingId and PaymentType) - no issues with that AND have output of 1 row with columns of unique PaymentType - issue with this step. So each BillingId has only 1 row ...
- 401
1
vote
1
answer
49
views
Single columns from several rows into several columns in one record, but allow NULL in the rows
I'm trying to combine single rows from multiple records into several columns in one record.
Say we have a database of people and they've all chosen 2 numbers. But, some people have only chosen 1 ...
- 79
1
vote
1
answer
244
views
Room/SQLite returning two different results in one query
So I've got the following queries:
//returns min max value my odomoter field must have
@Query("SELECT MAX(odometer) FROM MaintenanceRecord WHERE vehicleId = :maintenanceVehicleId AND " +
...
- 537
2
votes
3
answers
402
views
Finding duplicate values in multiple colums in a SQL table and count for chars
referring to this question:
Finding duplicate values in multiple colums in a SQL table and count
I have the following table structure:
id name1 name2 name3 ...
1 Hans Peter Frank
2 Hans Frank ...
- 57
1
vote
1
answer
125
views
How to I get the mysql average of a join with conditions?
I am trying to get the average ratings of a user by project type but include all users that have ratings regardless of type.
SELECT projects.user_id, AVG(ratings.rating) AS avg1
FROM projects
JOIN ...
- 33
1
vote
1
answer
852
views
get sum of distinct records and compare between two date ranges
I have a table called purchases, with columns: name, amount, city, date.
I want to return the sum of amount and order it by city with most sum of amount, and compare two date ranges.
Here is a fiddle: ...
- 12.4k
2
votes
1
answer
63
views
SELECT MySQL with 02 counts in same table with WHERE and GROUP BY in same Query
I am trying to do 02 COUNTS in same table, using WHERE and GROUP BY, but the result of the last column is coming wrong...
Thats the command SQL which I writed till now:
(SELECT estado_sigla,
...
- 478
1
vote
1
answer
556
views
greatest n per group optimization
How can I optimize this SQLite query? It currently takes 31 seconds to run. I want to display the top 10 stock gainers and top 10 stock losers on a web application. The table has 2m rows and should ...
- 409
1
vote
1
answer
45
views
Group rows by Id and concatenate month & order_count as columns?
Currently I have an orders table that is formatted with a row per month:
id
order_month
order_count
order_sum
111
2021-07
5
50
111
2021-08
10
50
111
2021-09
1
100
222
2021-07
8
80
222
2021-08
2
50
222
...
- 458
1
vote
2
answers
95
views
SQL Insert into duplicates
My code is as follows:
SELECT DISTINCT
lc.locationName,
(SELECT SUM(c.TimeSlotsCount) FROM Schedule s
WHERE s.ServiceId = sv.ServiceId
AND sv.LocationId = lc.LocationId
...
- 61
3
votes
2
answers
2k
views
Select count of multiple columns WHERE another column is distinct
I need to find out how many people ordered what type of food. My table looks something like this:
PersonId (int)
Hamburger (varchar100)
Fries (varchar100)
Soda (varchar100)
1
"Unique burger"
...
- 73
0
votes
1
answer
72
views
Weird MySQL JOIN where I want to include matches AND no matches from join
I have a need to get a specific result for a query... I believe the solution is a self-join of the same table... but it's not working as I would expect.
A bit of background: the "timeframe" ...
- 77