Issue with nested query - 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

Issue with nested query

Author

Topic

neharaikar20
Starting Member

1 Post

Posted - 2010-10-30 : 02:33:52

I have the following sql query for displaying data in Gridview of an asp.net page.

SELECT PLANNED_DOWNTIME.SYSTEMNAME AS Hostname, PLANNED_DOWNTIME.DATETIME, DOWNTIME.DOWNMINS,
PLANNED_DOWNTIME.PDOWNTIME, PLANNED_DOWNTIME.UDOWNTIME
FROM (SELECT DATETIME, SUM(SYSDOWNMINS) AS DOWNMINS
FROM SYSDOWNTIME
WHERE (SYSDOWNMINS > 0)
GROUP BY DATETIME) AS DOWNTIME INNER JOIN
PLANNED_DOWNTIME ON DOWNTIME.DATETIME = PLANNED_DOWNTIME.DATETIME

However, even after using sum function, it is still showing duplicate values for some datetime.

I need to get the sum of sysdownmins for every system ( selection of systems )on every datetime.

Regards,
Neha Raikar

slimt_slimt
Aged Yak Warrior

746 Posts

Posted - 2010-10-30 : 07:24:10

sure you might/will have duplicate values. this is because of your table PLANNED_DOWNTIME.
If you have any duplicate values in this table you will have duplicates in your return results.

you ought to use GROUP BY in out outer query or MIN/MAX/COUNT/SUM/AVG/... in your outer select list.

for example:
[/code]
SELECT
PLANNED_DOWNTIME.SYSTEMNAME AS Hostname
,PLANNED_DOWNTIME.DATETIME
,min(DOWNTIME.DOWNMINS) as min_downmins
,min(PLANNED_DOWNTIME.PDOWNTIME) as min_pdowntime
,min(PLANNED_DOWNTIME.UDOWNTIME) as min_udowntime
FROM
(
SELECT
DATETIME
,SUM(SYSDOWNMINS) AS DOWNMINS
FROM SYSDOWNTIME
WHERE (SYSDOWNMINS > 0)
GROUP BY
DATETIME
) AS DOWNTIME
INNER JOIN PLANNED_DOWNTIME
ON DOWNTIME.DATETIME = PLANNED_DOWNTIME.DATETIME

GROUP BY
PLANNED_DOWNTIME.SYSTEMNAME
,PLANED_DOWNTIME.DATETIME
[/code]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources