How to get distinct results - 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

How to get distinct results

Author

Topic

Kolipaka
Starting Member

7 Posts

Posted - 2008-10-17 : 08:02:09

Hi Guys,

I am new to SQL Server. When I am trying to execute the below query I am getting result as follows. but i need to get distinct results.
****************************
SELECT
count(distinct CONVERT(VARCHAR(2), new, 3)),sum(CST.case_id)
FROM
CASE_STATUS_TIMESTAMP CST, OPEN_REMEDY_TICKETS ORT
WHERE
NEW BETWEEN '10/06/2008' AND '10/10/2008' AND
(CST.CASE_ID = ORT.CASE_ID AND ORT.CATEGORY = 'Level3 Enabled Portal')
GROUP BY
(CONVERT(VARCHAR(2), new, 3))
UNION
SELECT
(CONVERT(VARCHAR(2), new, 3)) as day ,count(CST.case_id) as count
FROM
CASE_STATUS_TIMESTAMP CST, REMEDY_TICKETS RT
WHERE
NEW BETWEEN '10/06/2008' AND '10/10/2008' AND
(CST.CASE_ID = RT.CASE_ID AND RT.CATEGORY = 'Level3 Enabled Portal')
GROUP BY
(CONVERT(VARCHAR(2), new, 3))

Result is :

Day Count

06 5

07 1

07 17

08 2

08 10

09 5

09 2

****************

But I need result like this

Day Count

06 5
07 18
08 12
09 7
**********************************************

How can I write the query. Please help me on this.
Thanks in advance.

Regards,
Kolipaka

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-17 : 08:06:30

[code]SELECT day,SUM(Count)
FROM
(
SELECT
count(distinct CONVERT(VARCHAR(2), new, 3)) AS day,sum(CST.case_id) AS Count
FROM
CASE_STATUS_TIMESTAMP CST, OPEN_REMEDY_TICKETS ORT
WHERE
NEW BETWEEN '10/06/2008' AND '10/10/2008' AND
(CST.CASE_ID = ORT.CASE_ID AND ORT.CATEGORY = 'Level3 Enabled Portal')
GROUP BY
(CONVERT(VARCHAR(2), new, 3))
UNION
SELECT
(CONVERT(VARCHAR(2), new, 3)) as day ,count(CST.case_id) as count
FROM
CASE_STATUS_TIMESTAMP CST, REMEDY_TICKETS RT
WHERE
NEW BETWEEN '10/06/2008' AND '10/10/2008' AND
(CST.CASE_ID = RT.CASE_ID AND RT.CATEGORY = 'Level3 Enabled Portal')
GROUP BY
(CONVERT(VARCHAR(2), new, 3))
)t
GROUP BY day[/code]

onlyforme
Starting Member

25 Posts

Posted - 2008-10-18 : 02:18:46

SELECT distinct
count(CONVERT(VARCHAR(2), new, 3)),sum(CST.case_id)
FROM
CASE_STATUS_TIMESTAMP CST, OPEN_REMEDY_TICKETS ORT
WHERE
NEW BETWEEN '10/06/2008' AND '10/10/2008' AND
(CST.CASE_ID = ORT.CASE_ID AND ORT.CATEGORY = 'Level3 Enabled Portal')
GROUP BY
(CONVERT(VARCHAR(2), new, 3))
UNION
SELECT
(CONVERT(VARCHAR(2), new, 3)) as day ,count(CST.case_id) as count
FROM
CASE_STATUS_TIMESTAMP CST, REMEDY_TICKETS RT
WHERE
NEW BETWEEN '10/06/2008' AND '10/10/2008' AND
(CST.CASE_ID = RT.CASE_ID AND RT.CATEGORY = 'Level3 Enabled Portal')
GROUP BY
(CONVERT(VARCHAR(2), new, 3))

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-18 : 02:20:56

quote:
Originally posted by onlyforme

SELECT distinct
count(CONVERT(VARCHAR(2), new, 3)),sum(CST.case_id)
FROM
CASE_STATUS_TIMESTAMP CST, OPEN_REMEDY_TICKETS ORT
WHERE
NEW BETWEEN '10/06/2008' AND '10/10/2008' AND
(CST.CASE_ID = ORT.CASE_ID AND ORT.CATEGORY = 'Level3 Enabled Portal')
GROUP BY
(CONVERT(VARCHAR(2), new, 3))
UNION
SELECT
(CONVERT(VARCHAR(2), new, 3)) as day ,count(CST.case_id) as count
FROM
CASE_STATUS_TIMESTAMP CST, REMEDY_TICKETS RT
WHERE
NEW BETWEEN '10/06/2008' AND '10/10/2008' AND
(CST.CASE_ID = RT.CASE_ID AND RT.CATEGORY = 'Level3 Enabled Portal')
GROUP BY
(CONVERT(VARCHAR(2), new, 3))

you're grouping by new and then taking count of it? this will always return count as 1

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources