473,322 Members | 1,473 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,322 software developers and data experts.

query using where,group by,having

AdusumalliGopikumar
can anybody write a query using where,group by ,and having and explain me
Aug 26 '07 #1
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.
Aug 26 '07 #2
qhjghz
26
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
Aug 28 '07 #3
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));
Aug 29 '07 #4
qhjghz
26
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.
Aug 30 '07 #5
qhjghz
26
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
Aug 30 '07 #6

Sign in to post your reply or Sign up for a free account.

Similar topics

29
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"...
5
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...
0
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...
3
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...
2
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
4
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...
7
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...
7
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...
24
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...
8
by: s_wadhwa | last post by:
SELECT DISTINCTROW "01C" AS dummy, Buildings.BuildingNumber, UCASE(Buildings.BuildingName) AS BuildingName, Buildings.MasterPlanCode, Buildings.UniformBuildingCode,...
0
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...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
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...
0
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...
0
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...
1
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)...
1
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...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
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....

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.