Using Aggregate Functions in sql

Question

Hoe can I do following thing without using a VIEW from one Query.

!--CREATE THE VIEW

CREATE OR REPLACE VIEW BDGTMGR
AS
  SELECT MANAGERID,SUM(BUDGET) AS BDGT FROM 
  N_DEPT GROUP BY MANAGERID ;

!-- THEN GET RESULT FROM THE VIEW

SELECT MANAGERID FROM BDGTMGR WHERE BDGT = (select MAX(BDGT) FROM BDGTMGR);

Here N_DEPT is may original Table which has columns named DID, MANAGERID and BUDGET.

I want to get MANAGERID who controls Maximum Budget. A Manager can control more than one Department. DID is the primary key for this table.

How can I do this?

mechanical_meat · Accepted Answer · 2012-06-23 04:25:09Z

2

select   MANAGERID,SUM(BDGT) 
from     N_DEPT 
group by MANAGERID 
order by SUM(BDGT) desc limit 1

edited Jun 23, 2012 at 4:25

mechanical_meat

170k25 gold badges237 silver badges231 bronze badges

answered Jun 22, 2012 at 9:10

manurajhada

5,4004 gold badges28 silver badges43 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

manurajhada Over a year ago

assuming you are using mysql placed limit, if you are using sql server use top 1 or if you are using oracle use rownum=1

aF. · Accepted Answer · 2012-06-22 09:06:14Z

1

You can do it like this:

SELECT aux.MANAGERID
FROM
  (SELECT MANAGERID,
         SUM(BUDGET) AS BDGT
   FROM N_DEPT
   GROUP BY MANAGERID) aux
INNER JOIN BDGTMGR b ON b.MANAGERID = aux.MANAGERID
WHERE b.BDGT = (select MAX(BDGT) FROM BDGTMGR);

answered Jun 22, 2012 at 9:06

aF.

67k45 gold badges141 silver badges201 bronze badges

Comments

geekchic · Accepted Answer · 2012-06-22 09:08:07Z

0

The following query will work (modify depending on your flavour of SQL):

SELECT TOP 1 MANAGERID FROM N_DEPT GROUP BY MANAGERID ORDER BY SUM(BUDGET) DESC

answered Jun 22, 2012 at 9:08

geekchic

1,5661 gold badge11 silver badges21 bronze badges

1 Comment

aF. Over a year ago

this will get all managerid's, possibly you need to make a top 1 ;) (or limit)

Collectives™ on Stack Overflow

Using Aggregate Functions in sql

3 Answers 3

1 Comment

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related