(Sql Subqueries with 3 tables joined) With Count and Avg comparisson [on hold]2019 Community Moderator ElectionAdd a column with a default value to an existing table in SQL ServerSQL update query using joinsCount(*) vs Count(1) - SQL ServerHow can I do an UPDATE statement with JOIN in SQL?SQL Server: How to Join to first rowFinding duplicate values in a SQL tableFind all tables containing column with specified name - MS SQL ServerHow to Delete using INNER JOIN with SQL Server?Count and Avg in query to determine if result is greater than xhow to select from multiple genres in the same column
Called into a meeting and told we are being made redundant (laid off) and "not to share outside". Can I tell my partner?
ER diagram relationship node size adjustment
From an axiomatic set theoric approach why can we take uncountable unions?
Why restrict private health insurance?
Why is a very small peak with larger m/z not considered to be the molecular ion?
QQ Plot and Shapiro Wilk Test Disagree
What is the generally accepted pronunciation of “topoi”?
How to design an organic heat-shield?
What is the population of Romulus in the TNG era?
How do spaceships determine each other's mass in space?
Is this Paypal Github SDK reference really a dangerous site?
What sort of fish is this
When Schnorr signatures are part of Bitcoin will it be possible validate each block with only one signature validation?
Making a kiddush for a girl that has hard time finding shidduch
What is Tony Stark injecting into himself in Iron Man 3?
Possible to detect presence of nuclear bomb?
Can the alpha, lambda values of a glmnet object output determine whether ridge or Lasso?
NASA's RS-25 Engines
Is it safe to abruptly remove Arduino power?
Does "Until when" sound natural for native speakers?
Why does cron require MTA for logging?
Why does Solve lock up when trying to solve the quadratic equation with large integers?
Recommendation letter by significant other if you worked with them professionally?
Doubts in understanding some concepts of potential energy
(Sql Subqueries with 3 tables joined) With Count and Avg comparisson [on hold]
2019 Community Moderator ElectionAdd a column with a default value to an existing table in SQL ServerSQL update query using joinsCount(*) vs Count(1) - SQL ServerHow can I do an UPDATE statement with JOIN in SQL?SQL Server: How to Join to first rowFinding duplicate values in a SQL tableFind all tables containing column with specified name - MS SQL ServerHow to Delete using INNER JOIN with SQL Server?Count and Avg in query to determine if result is greater than xhow to select from multiple genres in the same column
How can I count the rental quantity and compare the result to the average rental quantity. after that I need to display the Title of the movie that has greater rental qtty than the average.
TblMovie
MovID
Title
MediaType
Price
QttyofDisc
DateAdded
Genre
TblBorrowDetails
BorrowNo
MovID
TblBorrow
BorrowNo
BorrowDate
MemberID
To Count how many Times The Movie was borrowed I Counted(BorrowNo)
sql database visual-studio
asked Mar 7 at 4:49
chris chanchris chan
1
New contributor
chris chan is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
put on hold as too broad by Vogel612, greg-449, EdChum, Matteo Baldi, FluffyKitten Mar 7 at 9:06
Please edit the question to limit it to a specific problem with enough detail to identify an adequate answer. Avoid asking multiple distinct questions at once. See the How to Ask page for help clarifying this question. If this question can be reworded to fit the rules in the help center, please edit the question.
add a comment |
How can I count the rental quantity and compare the result to the average rental quantity. after that I need to display the Title of the movie that has greater rental qtty than the average.
TblMovie
MovID
Title
MediaType
Price
QttyofDisc
DateAdded
Genre
TblBorrowDetails
BorrowNo
MovID
TblBorrow
BorrowNo
BorrowDate
MemberID
To Count how many Times The Movie was borrowed I Counted(BorrowNo)
sql database visual-studio
asked Mar 7 at 4:49
chris chanchris chan
1
New contributor
chris chan is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
put on hold as too broad by Vogel612, greg-449, EdChum, Matteo Baldi, FluffyKitten Mar 7 at 9:06
Please edit the question to limit it to a specific problem with enough detail to identify an adequate answer. Avoid asking multiple distinct questions at once. See the How to Ask page for help clarifying this question. If this question can be reworded to fit the rules in the help center, please edit the question.
you need to provide the sample data and expected output also tag the appropriate database like 'mysql', 'sql server' or any other db which you are using
– PSK
Mar 7 at 4:50
add a comment |
How can I count the rental quantity and compare the result to the average rental quantity. after that I need to display the Title of the movie that has greater rental qtty than the average.
TblMovie
MovID
Title
MediaType
Price
QttyofDisc
DateAdded
Genre
TblBorrowDetails
BorrowNo
MovID
TblBorrow
BorrowNo
BorrowDate
MemberID
To Count how many Times The Movie was borrowed I Counted(BorrowNo)
sql database visual-studio
asked Mar 7 at 4:49
chris chanchris chan
1
New contributor
chris chan is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
How can I count the rental quantity and compare the result to the average rental quantity. after that I need to display the Title of the movie that has greater rental qtty than the average.
TblMovie
MovID
Title
MediaType
Price
QttyofDisc
DateAdded
Genre
TblBorrowDetails
BorrowNo
MovID
TblBorrow
BorrowNo
BorrowDate
MemberID
To Count how many Times The Movie was borrowed I Counted(BorrowNo)
sql database visual-studio
sql database visual-studio
asked Mar 7 at 4:49
chris chanchris chan
1
New contributor
chris chan is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
asked Mar 7 at 4:49
chris chanchris chan
1
New contributor
chris chan is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
asked Mar 7 at 4:49
chris chanchris chan
1
New contributor
chris chan is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
asked Mar 7 at 4:49
chris chanchris chan
1
asked Mar 7 at 4:49
chris chanchris chan
1
1
New contributor
chris chan is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
New contributor
chris chan is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
chris chan is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
put on hold as too broad by Vogel612, greg-449, EdChum, Matteo Baldi, FluffyKitten Mar 7 at 9:06
Please edit the question to limit it to a specific problem with enough detail to identify an adequate answer. Avoid asking multiple distinct questions at once. See the How to Ask page for help clarifying this question. If this question can be reworded to fit the rules in the help center, please edit the question.
put on hold as too broad by Vogel612, greg-449, EdChum, Matteo Baldi, FluffyKitten Mar 7 at 9:06
Please edit the question to limit it to a specific problem with enough detail to identify an adequate answer. Avoid asking multiple distinct questions at once. See the How to Ask page for help clarifying this question. If this question can be reworded to fit the rules in the help center, please edit the question.
you need to provide the sample data and expected output also tag the appropriate database like 'mysql', 'sql server' or any other db which you are using
– PSK
Mar 7 at 4:50
add a comment |
you need to provide the sample data and expected output also tag the appropriate database like 'mysql', 'sql server' or any other db which you are using
– PSK
Mar 7 at 4:50
you need to provide the sample data and expected output also tag the appropriate database like 'mysql', 'sql server' or any other db which you are using
– PSK
Mar 7 at 4:50
you need to provide the sample data and expected output also tag the appropriate database like 'mysql', 'sql server' or any other db which you are using
– PSK
Mar 7 at 4:50
add a comment |
2 Answers
2
active
oldest
votes
You have not mentioned any database so not sure if row_number() with partition can be used or not, but you can achieve this using standard sql query like following.
select m.MovID,
m.Title
from (select m.MovID,
m.Title,
ctBorrow,
Avg(ctBorrow) avgBorrow
from (select m.MovID,
m.Title,
Count(br.BorrowNo) ctBorrow
from TblMovie m
inner join TblBorrowDetails tb
on tb.MovID = m.MovID
inner join TblBorrow br
on br.BorrowNo = tb.BorrowNo
group by m.MovID,
m.Title)t
group by m.MovID,
m.Title,
ctBorrow) t1
where ctBorrow > avgBorrow
answered Mar 7 at 5:03
PSKPSK
12.2k31633
add a comment |
I assume you need a list of movies which were borrowed more than the average of all borrowed movies.
The query below should get you that.
Please find the fiddle here I have removed some unwanted columns from the tables.
SELECT M.*
FROM TblMovie M
JOIN TblBorrowDetails BD1 ON M.MovID = BD1.MovID
GROUP BY M.MovID, M.Title
HAVING COUNT(BD1.MovID) >
(
SELECT t1.MAvg
FROM
(
SELECT AVG(MCount) AS MAvg
FROM
(
SELECT COUNT(BD2.MovID) AS MCount
FROM TblBorrowDetails BD2
GROUP BY BD2.MovID
)t
)t1
)
answered Mar 7 at 5:33
Priyank PanchalPriyank Panchal
380410
add a comment |
2 Answers
2
active
oldest
votes
2 Answers
2
active
oldest
votes
active
oldest
votes
active
oldest
votes
You have not mentioned any database so not sure if row_number() with partition can be used or not, but you can achieve this using standard sql query like following.
select m.MovID,
m.Title
from (select m.MovID,
m.Title,
ctBorrow,
Avg(ctBorrow) avgBorrow
from (select m.MovID,
m.Title,
Count(br.BorrowNo) ctBorrow
from TblMovie m
inner join TblBorrowDetails tb
on tb.MovID = m.MovID
inner join TblBorrow br
on br.BorrowNo = tb.BorrowNo
group by m.MovID,
m.Title)t
group by m.MovID,
m.Title,
ctBorrow) t1
where ctBorrow > avgBorrow
answered Mar 7 at 5:03
PSKPSK
12.2k31633
add a comment |
You have not mentioned any database so not sure if row_number() with partition can be used or not, but you can achieve this using standard sql query like following.
select m.MovID,
m.Title
from (select m.MovID,
m.Title,
ctBorrow,
Avg(ctBorrow) avgBorrow
from (select m.MovID,
m.Title,
Count(br.BorrowNo) ctBorrow
from TblMovie m
inner join TblBorrowDetails tb
on tb.MovID = m.MovID
inner join TblBorrow br
on br.BorrowNo = tb.BorrowNo
group by m.MovID,
m.Title)t
group by m.MovID,
m.Title,
ctBorrow) t1
where ctBorrow > avgBorrow
answered Mar 7 at 5:03
PSKPSK
12.2k31633
add a comment |
You have not mentioned any database so not sure if row_number() with partition can be used or not, but you can achieve this using standard sql query like following.
select m.MovID,
m.Title
from (select m.MovID,
m.Title,
ctBorrow,
Avg(ctBorrow) avgBorrow
from (select m.MovID,
m.Title,
Count(br.BorrowNo) ctBorrow
from TblMovie m
inner join TblBorrowDetails tb
on tb.MovID = m.MovID
inner join TblBorrow br
on br.BorrowNo = tb.BorrowNo
group by m.MovID,
m.Title)t
group by m.MovID,
m.Title,
ctBorrow) t1
where ctBorrow > avgBorrow
answered Mar 7 at 5:03
PSKPSK
12.2k31633
You have not mentioned any database so not sure if row_number() with partition can be used or not, but you can achieve this using standard sql query like following.
select m.MovID,
m.Title
from (select m.MovID,
m.Title,
ctBorrow,
Avg(ctBorrow) avgBorrow
from (select m.MovID,
m.Title,
Count(br.BorrowNo) ctBorrow
from TblMovie m
inner join TblBorrowDetails tb
on tb.MovID = m.MovID
inner join TblBorrow br
on br.BorrowNo = tb.BorrowNo
group by m.MovID,
m.Title)t
group by m.MovID,
m.Title,
ctBorrow) t1
where ctBorrow > avgBorrow
answered Mar 7 at 5:03
PSKPSK
12.2k31633
answered Mar 7 at 5:03
PSKPSK
12.2k31633
answered Mar 7 at 5:03
PSKPSK
12.2k31633
answered Mar 7 at 5:03
PSKPSK
12.2k31633
12.2k31633
add a comment |
add a comment |
I assume you need a list of movies which were borrowed more than the average of all borrowed movies.
The query below should get you that.
Please find the fiddle here I have removed some unwanted columns from the tables.
SELECT M.*
FROM TblMovie M
JOIN TblBorrowDetails BD1 ON M.MovID = BD1.MovID
GROUP BY M.MovID, M.Title
HAVING COUNT(BD1.MovID) >
(
SELECT t1.MAvg
FROM
(
SELECT AVG(MCount) AS MAvg
FROM
(
SELECT COUNT(BD2.MovID) AS MCount
FROM TblBorrowDetails BD2
GROUP BY BD2.MovID
)t
)t1
)
answered Mar 7 at 5:33
Priyank PanchalPriyank Panchal
380410
add a comment |
I assume you need a list of movies which were borrowed more than the average of all borrowed movies.
The query below should get you that.
Please find the fiddle here I have removed some unwanted columns from the tables.
SELECT M.*
FROM TblMovie M
JOIN TblBorrowDetails BD1 ON M.MovID = BD1.MovID
GROUP BY M.MovID, M.Title
HAVING COUNT(BD1.MovID) >
(
SELECT t1.MAvg
FROM
(
SELECT AVG(MCount) AS MAvg
FROM
(
SELECT COUNT(BD2.MovID) AS MCount
FROM TblBorrowDetails BD2
GROUP BY BD2.MovID
)t
)t1
)
answered Mar 7 at 5:33
Priyank PanchalPriyank Panchal
380410
add a comment |
I assume you need a list of movies which were borrowed more than the average of all borrowed movies.
The query below should get you that.
Please find the fiddle here I have removed some unwanted columns from the tables.
SELECT M.*
FROM TblMovie M
JOIN TblBorrowDetails BD1 ON M.MovID = BD1.MovID
GROUP BY M.MovID, M.Title
HAVING COUNT(BD1.MovID) >
(
SELECT t1.MAvg
FROM
(
SELECT AVG(MCount) AS MAvg
FROM
(
SELECT COUNT(BD2.MovID) AS MCount
FROM TblBorrowDetails BD2
GROUP BY BD2.MovID
)t
)t1
)
answered Mar 7 at 5:33
Priyank PanchalPriyank Panchal
380410
I assume you need a list of movies which were borrowed more than the average of all borrowed movies.
The query below should get you that.
Please find the fiddle here I have removed some unwanted columns from the tables.
SELECT M.*
FROM TblMovie M
JOIN TblBorrowDetails BD1 ON M.MovID = BD1.MovID
GROUP BY M.MovID, M.Title
HAVING COUNT(BD1.MovID) >
(
SELECT t1.MAvg
FROM
(
SELECT AVG(MCount) AS MAvg
FROM
(
SELECT COUNT(BD2.MovID) AS MCount
FROM TblBorrowDetails BD2
GROUP BY BD2.MovID
)t
)t1
)
answered Mar 7 at 5:33
Priyank PanchalPriyank Panchal
380410
answered Mar 7 at 5:33
Priyank PanchalPriyank Panchal
380410
answered Mar 7 at 5:33
Priyank PanchalPriyank Panchal
380410
answered Mar 7 at 5:33
Priyank PanchalPriyank Panchal
380410
380410
add a comment |
add a comment |
you need to provide the sample data and expected output also tag the appropriate database like 'mysql', 'sql server' or any other db which you are using
– PSK
Mar 7 at 4:50