## SQL problem in using dual aggregate function

Solved
Frequent Contributor
Posts: 81

# SQL problem in using dual aggregate function

Hi all,

I want a day vise average and then some day and night on a particular date so can anyone please explain me how can i do so..

data test;

input Date :date9. Shift\$ IPRN;

format date date11.;

datalines;

05Jan2014 Day 12

05Jan2014 Day 11

05Jan2014 Day 10

05Jan2014 Night 07

05Jan2014 Night 09

06Jan2014 Day 08

06Jan2014 Day 12

06Jan2014 Night 07

06Jan2014 Night 09

06Jan2014 Night 11

;

run;

Required result:

date               IPRN

05Jan2014     19

06Jan2014      19

Regards

Anand

Accepted Solutions
Solution
‎11-09-2014 07:54 AM
Super User
Posts: 10,784

## Re: SQL problem in using dual aggregate function

If I understood what you mean.

```

data test;
input Date :date9. Shift\$ IPRN;
format date date11.;
datalines;
05Jan2014 Day 12
05Jan2014 Day 11
05Jan2014 Day 10
05Jan2014 Night 07
05Jan2014 Night 09
06Jan2014 Day 08
06Jan2014 Day 12
06Jan2014 Night 07
06Jan2014 Night 09
06Jan2014 Night 11
;
run;

proc sql;
create table want as
select date,sum(iprn) as iprn
from (select date,shift,avg(iprn) as iprn from test group by date,shift)
group by  date;
quit;
```

Xia Keshan

All Replies
Posts: 1,147

## Re: SQL problem in using dual aggregate function

Please try , this will give you the day wise average, night wise average and day & night wise average (i believe this is the desired output you are expecting, however the average of that record for the given data would be 9.8)

proc sql;

create table test2 as select date,shift, mean(IPRN) as average from test group by date,shift

union

select date,'day & Night' as shift, mean(IPRN) as average from test group by date;

quit;

Thanks,
Jag

Thanks,
Jag
Frequent Contributor
Posts: 81

## Re: SQL problem in using dual aggregate function

Hi Jagdish,

Can we do this using nested select statement as I have to apply this logic in a large query have done before using nested query but am not getting that right now.

Posts: 1,147

## Re: SQL problem in using dual aggregate function

i believe we can achieve it , could you please provide the code into which you want to get this code, so i could try to provide a better suggestions. at the moment i am unable to imagine the scenario you are in.

Thanks,
Jag
Frequent Contributor
Posts: 81

## Re: SQL problem in using dual aggregate function

I am close to the result using my query as follows:

proc sql;

select date, shift, avg(IPRN)as IPRNo

from (select * from test having sum(iprn))

group by date, shift;

quit;

the original code is mentioned below:

create view TEMP_LASR_VIEW_5737 as

SELECT Production.CmpyCode, Production.Unit, Production.Iddate, Production.IdPlantCode, Production.IdShiftCode, Production.IdPrdCode, - SUM(Production.IdPrdQty) AS ProdQty, SAS_V_PrdMstMI.PrdBBpH,

CASE WHEN (SUM(SAS_V_PrdMstMI.PrdBBpH) IS NULL OR SUM(SAS_V_PrdMstMI.PrdBBpH)=0) THEN . ELSE ((CASE WHEN SUM(Production.IdPrdQty) IS NULL  THEN 0 ELSE -SUM(Production.IdPrdQty) END)/(CASE WHEN SUM(SAS_V_PrdMstMI.PrdBBpH) IS NULL THEN 0 ELSE SUM(SAS_V_PrdMstMI.PrdBBpH) END)) END

AS InHrs, SAS_V_PrdMst.PrdName

FROM libbnpl.SAS_V_FgTData AS Production INNER JOIN libbnpl.SAS_V_PrdMstMI AS SAS_V_PrdMstMI

ON Production.IdPrdCode = SAS_V_PrdMstMI.PrdCode AND Production.IdPlantCode = SAS_V_PrdMstMI.PrdPlantCode

INNER JOIN

libbnpl.SAS_V_PrdMst AS SAS_V_PrdMst

ON SAS_V_PrdMstMI.PrdCode = SAS_V_PrdMst.PrdCode

WHERE     (Production.IdType = 'PR') AND (Production.IdDelTag IS NULL OR

Production.IdDelTag <> 'Y')

GROUP BY Production.CmpyCode, Production.Unit, Production.Iddate, Production.IdPlantCode, Production.IdShiftCode, Production.IdPrdCode, SAS_V_PrdMstMI.PrdBBpH, SAS_V_PrdMst.PrdName

HAVING put(datepart(Production.Iddate), date9.) between put('01APR2012'd, date9.) AND put(today(), date9.)

ORDER BY Production.Iddate;

quit;

Solution
‎11-09-2014 07:54 AM
Super User
Posts: 10,784

## Re: SQL problem in using dual aggregate function

If I understood what you mean.

```

data test;
input Date :date9. Shift\$ IPRN;
format date date11.;
datalines;
05Jan2014 Day 12
05Jan2014 Day 11
05Jan2014 Day 10
05Jan2014 Night 07
05Jan2014 Night 09
06Jan2014 Day 08
06Jan2014 Day 12
06Jan2014 Night 07
06Jan2014 Night 09
06Jan2014 Night 11
;
run;

proc sql;
create table want as
select date,sum(iprn) as iprn
from (select date,shift,avg(iprn) as iprn from test group by date,shift)
group by  date;
quit;
```

Xia Keshan

Frequent Contributor
Posts: 81

## Re: SQL problem in using dual aggregate function

THANK YOU....XIA.

Your code is working fine...that is what I wanted

but still facing problem on applying this logic in my query so could you please do me one more favor to apply your logic in my query as I tried it many ways but couldn't succeed.

Regards

anand

Super User
Posts: 10,784

## Re: SQL problem in using dual aggregate function

That is too mess.  Your query contains too many variables and too many logic .

Post your original data and the output you need.

Xia Keshan

Frequent Contributor
Posts: 81

## Re: SQL problem in using dual aggregate function

providing the code is not possible

Super User
Posts: 10,784

## Re: SQL problem in using dual aggregate function

Sorry . Conquer it by yourself.

Super User
Posts: 23,752

## Re: SQL problem in using dual aggregate function

You can use two proc means very easily but it looks like you haven't posted your full problem.

data test;

input Date :date9. Shift\$ IPRN;

format date date11.;

datalines;

05Jan2014 Day 12

05Jan2014 Day 11

05Jan2014 Day 10

05Jan2014 Night 07

05Jan2014 Night 09

06Jan2014 Day 08

06Jan2014 Day 12

06Jan2014 Night 07

06Jan2014 Night 09

06Jan2014 Night 11

;

run;

proc means data=test sum;

class date shift;

type date*shift;

var IPRN;

output out=want1 mean=tot_iprn;

run;

proc means data=want1 sum;

class date;

var tot_iprn;

run;

🔒 This topic is solved and locked.