Newbie Help Aggregate Functions

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

Newbie Help Aggregate Functions

Author

Topic

WelshPunk
Yak Posting Veteran

67 Posts

Posted - 2008-09-02 : 03:31:15

Hello clever ones. I am trying to get a count of items using the following code. I have missed something out as it is not giving me a total of the individual departments - I am getting more than one row for each dept. Any help would be appreciated

SELECT
DISTINCT dbo.locations.location_name,
COUNT(DISTINCT dbo.safety_obs_data.form_id) AS TotalObs,
SUM(dbo.safety_obs_data.safe) AS TotalSafe,

SUM(dbo.safety_obs_data.unsafe) AS TotalUnsafe,
CONVERT (decimal(18 , 2), CASE WHEN dbo.safety_obs_data.safe + dbo.safety_obs_data.unsafe = 0 THEN NULL ELSE (SUM(dbo.safety_obs_data.safe) * 100.0) / (SUM(dbo.safety_obs_data.safe) + SUM(dbo.safety_obs_data.unsafe)) END) as [% Safe],

SUM(CASE WHEN unobserved = 1 THEN 1 ELSE NULL END) AS NotSeen,
SUM(CASE WHEN made_safe = 1 THEN 1 ELSE NULL END) AS TotalMadeSafe,
SUM(CASE WHEN sap_note = 1 THEN 1 ELSE NULL END) AS TotalSAPNote,
SUM(CASE WHEN honk = 1 THEN 1 ELSE NULL END) AS TotalHonks,
dbo.safety_obs_data.location_id

FROM
dbo.safety_obs_data

INNER JOIN
dbo.locations ON dbo.safety_obs_data.location_id = dbo.locations.location_id

WHERE
(dbo.safety_obs_data.create_dte BETWEEN CONVERT (DATETIME, '01-Aug-2008', 102) AND CONVERT (DATETIME, '29-Aug-2009' , 102))

GROUP BY
dbo.safety_obs_data.location_id,
dbo.locations.location_name,
dbo.safety_obs_data.safe

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-09-02 : 03:53:46

If you want one row per department remove all except department related field from GROUP BY (i'm not sure which field has dept related info in posted code)

WelshPunk
Yak Posting Veteran

67 Posts

Posted - 2008-09-02 : 03:59:07

But these in the group by are used in the query using aggregate functions so they need to be there or I will get errors. Unless there is a way around this

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-09-02 : 04:02:35

WHy have you used dbo.safety_obs_data.safe in group by when you're already applying aggregate function on it? remove and try

WelshPunk
Yak Posting Veteran

67 Posts

Posted - 2008-09-02 : 04:06:39

When I remove all from group by except the location_name I get this

Server: Msg 8120, Level 16, State 1, Line 3
Column 'dbo.safety_obs_data.safe' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
Server: Msg 8120, Level 16, State 1, Line 3
Column 'dbo.safety_obs_data.unsafe' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
Server: Msg 8120, Level 16, State 1, Line 3
Column 'dbo.safety_obs_data.location_id' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-09-02 : 04:24:39

quote:
Originally posted by WelshPunk

When I remove all from group by except the location_name I get this

Server: Msg 8120, Level 16, State 1, Line 3
Column 'dbo.safety_obs_data.safe' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
Server: Msg 8120, Level 16, State 1, Line 3
Column 'dbo.safety_obs_data.unsafe' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
Server: Msg 8120, Level 16, State 1, Line 3
Column 'dbo.safety_obs_data.location_id' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

Another thing you could do is to perform aggregation in derived table and then join it with main table to get values.

WelshPunk
Yak Posting Veteran

67 Posts

Posted - 2008-09-02 : 04:29:22

Sorry I am a newbie - could you give me an example please. I was contemplating creating a temp table and slowly populate it one bit at a time. Would this be easier?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-09-02 : 04:34:45

whats field on which you need value to aggregated on? the one related to department

WelshPunk
Yak Posting Veteran

67 Posts

Posted - 2008-09-02 : 04:47:48

I need the format to look like this
Location Name Total Obs Total Safe Total Unsafe % Safe Not Seen Made Safe Total Sap Note Total Honks
Admin Office Block 1 0 0 NULL 3 NULL NULL NULL

With just one distinct location per row and totals for all other fields in that row

WelshPunk
Yak Posting Veteran

67 Posts

Posted - 2008-09-02 : 06:07:20

Hey I fixed it myself!!!!!

The problem was that in the %Safe column I was using the fields safe and unsafe to get the % safe. All I needed to do was use SUM on both of them and this fixed the problem. I removed all except location_name from the group by and it works a treat. I'm the man!

THanks everyone

shanmp
Starting Member

13 Posts

Posted - 2008-09-02 : 06:07:37

Hi,
I just went through ur query, u have mentioned SUM(dbo.safety_obs_data.safe) and the same column you have used in the group by function also. Please remove the column from the group by function and run the query , you may get the correct result.

WelshPunk
Yak Posting Veteran

67 Posts

Posted - 2008-09-02 : 06:10:56

We must have posted at the same time. I have resolved the issue. See my prevous post

Sollution

SELECT
dbo.locations.location_name,
COUNT(DISTINCT dbo.safety_obs_data.form_id) AS TotalObs,
SUM(dbo.safety_obs_data.safe) AS TotalSafe,

SUM(dbo.safety_obs_data.unsafe) AS TotalUnsafe,
CONVERT (decimal(18 , 2), CASE WHEN SUM(dbo.safety_obs_data.safe) + SUM(dbo.safety_obs_data.unsafe) = 0 THEN NULL ELSE (SUM(dbo.safety_obs_data.safe) * 100.0) / (SUM(dbo.safety_obs_data.safe) + SUM(dbo.safety_obs_data.unsafe)) END) as [% Safe],

SUM(CASE WHEN unobserved = 1 THEN 1 ELSE NULL END) AS NotSeen,
SUM(CASE WHEN made_safe = 1 THEN 1 ELSE NULL END) AS TotalMadeSafe,
SUM(CASE WHEN sap_note = 1 THEN 1 ELSE NULL END) AS TotalSAPNote,
SUM(CASE WHEN honk = 1 THEN 1 ELSE NULL END) AS TotalHonks

FROM
dbo.safety_obs_data

INNER JOIN
dbo.locations ON dbo.safety_obs_data.location_id = dbo.locations.location_id

WHERE
(dbo.safety_obs_data.create_dte BETWEEN CONVERT (DATETIME, '01-Aug-2008', 102) AND CONVERT (DATETIME, '29-Aug-2009' , 102))

GROUP BY

dbo.locations.location_name

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources