can anybody write a query using where,group by ,and having and explain me
5 4056 Saii 145
Expert 100+
Can you please post the query you wrote related to the same and if there is anything missing, anyone can explain.
can anybody write a query using where,group by ,and having and explain me
select empno,count(*) from emp group by empno having count(empno)>1
This query finds out the rows in emp with same empno
A query using where clause,
group by clause,
having clause
and order by clause.
i got it
select ename,job,deptno,sal
from emp where sal>=2500 group by deptno,job,sal,ename having deptno =10 order by ename desc;
or
select ename,sal
from emp where sal=(select avg(sal)
from emp group by deptno having deptno not in(10,20));
A query using where clause,
group by clause,
having clause
and order by clause.
i got it
select ename,job,deptno,sal
from emp where sal>=2500 group by deptno,job,sal,ename having deptno =10 order by ename desc;
or
select ename,sal
from emp where sal=(select avg(sal)
from emp group by deptno having deptno not in(10,20));
I won't suggest the queries you have given .. Just think what extra are you doing with the HAVING caluse which you could not have done with your WHERE clause. Nothing. Don't use HAVING in this regard. It affexts performance. But see the query I posted.
A query using where clause,
group by clause,
having clause
and order by clause.
i got it
select ename,job,deptno,sal
from emp where sal>=2500 group by deptno,job,sal,ename having deptno =10 order by ename desc;
or
select ename,sal
from emp where sal=(select avg(sal)
from emp group by deptno having deptno not in(10,20));
I won't suggest the queries you have given .. Just think what extra are you doing with the HAVING caluse which you could not have done with your WHERE clause. Nothing. Don't use HAVING in this regard. It affects performance. But see the query I posted. There was no alternative way to do it via WHERE clause because it was FILTERING BASED ON THE AGGREGATE(ie the count). The filtering was NOT ROW BY ROW in that query. REMEMBER THAT HAVING IS AN AGGREGATE CLAUSE. I hope this makes things more clear
Sign in to post your reply or Sign up for a free account.
Similar topics
by: shank |
last post by:
1) I'm getting this error: Syntax error (missing operator) in query
expression on the below statement. Can I get some advice.
2) I searched ASPFAQ and came up blank. Where can find the "rules"...
|
by: Jerome |
last post by:
Hi,
I've got the following problem:
I want my ASP page to display a certain number (based on a COUNT query),
it works fine if the result is at least 1! If there are no records to be
counted...
|
by: unixman |
last post by:
As usual, it is 2:00am, and I'm pulling my hair out, finally resorting to
posting in the newsgroups for help. :)
Simple problem, in theory.
Given table "map":
CREATE TABLE map (
entry_id...
|
by: Jonathan |
last post by:
Hi all!
For a match schedule I would like to find all possible combinations of
teams playing home and away (without teams playing to themselves of course).
I now the simple version works...
|
by: Sebastian |
last post by:
The following query needs about 2 minutes to complete (finding dupes)
on a table of about 10000 addresses. Does anyone have an idea on how
to speed this up ?
Thanks in advance !!!
Sebastian
|
by: Mark |
last post by:
Hi all,
I am currently in the design stages of a database for work. I have come
up with a way to get informaion I need using a union query but was wandering
what effects this will have on...
|
by: Riley DeWiley |
last post by:
I am continually amazed by SQL's ability to humble me ....
I have a toy query into a toy database that looks just like this:
SELECT .
FROM f, fw, w
WHERE f.id = fw.fid and fw.wid = w.id and...
|
by: serge |
last post by:
How can I run a single SP by asking multiple sales question either
by using the logical operator AND for all the questions; or using
the logical operator OR for all the questions. So it's always...
|
by: clare at snyder.on.ca |
last post by:
I have a SQL query I need to design to select name and email addresses
for policies that are due and not renewed in a given time period. The
problem is, the database keeps the information for every...
|
by: s_wadhwa |
last post by:
SELECT DISTINCTROW "01C" AS dummy, Buildings.BuildingNumber,
UCASE(Buildings.BuildingName) AS BuildingName,
Buildings.MasterPlanCode, Buildings.UniformBuildingCode,...
|
by: DolphinDB |
last post by:
Tired of spending countless mintues downsampling your data? Look no further!
In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
|
by: ryjfgjl |
last post by:
ExcelToDatabase: batch import excel into database automatically...
|
by: isladogs |
last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM).
In this month's session, we are pleased to welcome back...
|
by: Vimpel783 |
last post by:
Hello!
Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
|
by: ArrayDB |
last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
|
by: PapaRatzi |
last post by:
Hello,
I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
|
by: CloudSolutions |
last post by:
Introduction:
For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
|
by: Defcon1945 |
last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
|
by: Shællîpôpï 09 |
last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
| | |