Help in solving SQL quary - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Help in solving SQL quary

Author

Topic

divya317
Starting Member

16 Posts

Posted - 2007-07-06 : 14:28:15

Hi,

I am new to the forum. I need help in solving the following quary.

Table structure:
Region: region_id, name
Employee: employee_id, name, region_id
Sales: sales_id, employee_id, sale_date, sale_amount
-there’s an individual entry in the sales table for each sale

Problem
Write a query that returns a list of the Regions and the number of employees in each region. The employee only gets added to the count if they had total sales greater than $50,000 last month. Sort the information by the number of employees per region who fit this criteria.

Thanks,
Divya

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-07-06 : 14:58:28

We do not do other people's homework here unless you've at least shown that you've tried to work on the problem yourself.

So what have you tried so far?

Tara Kizer
http://weblogs.sqlteam.com/tarad/

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-07-06 : 15:02:11

put your aggregated data for employees into a subquery that you join to the main region table.

hints: Group By, Having, Where, Count(*)

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp

divya317
Starting Member

16 Posts

Posted - 2007-07-06 : 15:07:26

Hi ,
Thanks for reply.. Well I find following part is diffcult
"The employee only gets added to the count if they had total sales greater than $50,000 last month."

From this quary I can get name of the employee and the total salary they done so far .. Here is my quary.

SELECT A1.name, SUM(A2.Sales) SALES
FROM Employee A1, sale A2
WHERE A1.name = A2.name (+)
GROUP BY A1.name

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-07-06 : 15:09:08

Take a look at HAVING clause.

Tara Kizer
http://weblogs.sqlteam.com/tarad/

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-07-06 : 15:09:57

The use of + sign indicates this isn't a SQL Server question. What dbms are you using?

Tara Kizer
http://weblogs.sqlteam.com/tarad/

divya317
Starting Member

16 Posts

Posted - 2007-07-06 : 15:11:26

Oracle

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-07-06 : 15:12:45

You should post your questions on a site that deals with Oracle then. This site is for Microsoft SQL Server.

Tara Kizer
http://weblogs.sqlteam.com/tarad/

divya317
Starting Member

16 Posts

Posted - 2007-07-06 : 15:15:16

I dont think there is much difference in SQL n PL-SQL... Any ways if you guys not intersted in helping then fine. No problem..
Thanks for your help so far :)

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-07-06 : 15:19:20

it's not that we're not interested it's just that this is a site for Microsoft's SQL Server
and most people here have no idea about syntax, etc...
of other db engines.

in sql server you'd just add
Having > 50000

after your group by

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-07-06 : 15:20:33

I already said to check out HAVING clause. I've never worked on Oracle, so I don't know if it'll work there, but it definitely solves your problem in SQL Server.

Tara Kizer
http://weblogs.sqlteam.com/tarad/

divya317
Starting Member

16 Posts

Posted - 2007-07-06 : 15:52:49

Oh!! I am sorry.
Anyways, I am able to solve my problem with the help of you guys.. so

THANKS YOU :)
Divya

divya317
Starting Member

16 Posts

Posted - 2007-07-06 : 17:31:50

Hi Again,

Well I still facing a problem in my quary.

I have written it so far as :
SELECT A1.name, count(DISTINCT A2.employee_id)
FROM region A1, employee A2 , sale A3
WHERE A1.region_id = A2.region_id
GROUP BY A1.name
having sum( A3.sale_amount) > 50000

But this quary add the all the sale of the employee + I need to check the last month sale too. As per the following line:
"The employee only gets added to the count if they had total sales greater than $50,000 last month.".

Now, how could I check the date of last in SQL.

Thanks,
Divya

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-07-06 : 17:38:32

Add that condition to your WHERE clause.

Tara Kizer
http://weblogs.sqlteam.com/tarad/

aarumugavel
Starting Member

8 Posts

Posted - 2007-11-22 : 07:14:39

SELECT A.REGION_ID,A.REGION_NAME,B.EMPLOYEE_ID,B.NAME
SUM(SALES_AMUONT) FROM REGION A INNER JOIN EMPLOYEE B ON A.REGION_ID = B.REGION_ID
INNER JOIN SALES C ON B.EMPLOYEE_ID = C.EMPLOYEE_ID
AND SALES_DATE BETWEEN 'FROMDATE' AND 'TODATE'
GROUP BY A.REGIOD_ID,
A.REGION_ID,A.REGION_NAME,B.EMPLOYEE_ID,B.NAME
HAVING SUM(SALES_AMOUNT) > 50000
ORDER BY A.REGIOD_ID,B.EMPLOYEE_ID

biswanath23
Starting Member

4 Posts

Posted - 2009-07-31 : 01:27:59

Select ForumTypeDetails.ForumTypeID,max(forumdetails.date) as Date,ForumTypeDetails.ForumName,ForumTypeDetails.ForumDetails,
count(forumdetails.ForumTypeID)as Posting,forumdetails.userID
From ForumTypeDetails
Left Join forumdetails
On forumdetails.ForumTypeID =ForumTypeDetails.ForumTypeID and forumdetails.ParentID=0
Group by ForumTypeDetails.ForumTypeID,
ForumTypeDetails.ForumTypeID,ForumTypeDetails.ForumName,ForumTypeDetails.ForumDetails,forumdetails.userID

Table:forumdetails >> MessageID,ParentID,userID,Subject,Message,Date,IsBlocked,ForumTypeID

Table:ForumTypeDetails >>ForumTypeID,ForumName,ForumDetails,IsBlocked

i also want to count the reply under the Posting in a single query(other fields are also needed),can any one help me !!
Thanks .

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2009-08-02 : 01:15:29

biswanath23, you should start a new thread.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

"Let's begin with the premise that everything you've done up until this point is wrong."

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources