Count and Distinct - 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

SQL Server 2000 Forums

SQL Server Development (2000)

Count and Distinct

Author

Topic

Jeepaholic
Starting Member

36 Posts

Posted - 2002-07-14 : 03:58:55

Hi there. The following SQL statement returns 621 records:


SELECT DISTINCT reportElements.element_id, reportElements.name
 FROM reportElements, reportTechnologyTypes, reportTechnology_Element, reportGroup_Elements, reportGroups
 WHERE reportElements.element_type = reportTechnology_Element.element_type
 AND reportTechnology_Element.TechnologyTypeID = reportTechnologyTypes.TechnologyTypeID
 AND reportGroup_Elements.element_id = reportElements.element_id
 AND reportGroup_Elements.group_id = reportGroups.group_id
 AND reportTechnologyTypes.TechnologyID = 'BlahHa'
 AND reportGroups.name LIKE 'Hello%'

.
.
I'm trying to simply get a COUNT (the number 621) from this query, but am having troubles. I've tried varying forms of COUNT and DISTINCT and this and that, have read and read trying to find a solution - but keep coming up with either a wrong number (too high), or error messages. I'm thinking this should be pretty easy, but I'm just not getting it...

Thanks for any help!

Edited by - Jeepaholic on 07/14/2002 04:58:41

Nazim
A custom title

1408 Posts

Posted - 2002-07-14 : 08:24:19

select count(1)
from (
SELECT DISTINCT reportElements.element_id, reportElements.name
FROM reportElements, reportTechnologyTypes, reportTechnology_Element, reportGroup_Elements, reportGroups
WHERE reportElements.element_type = reportTechnology_Element.element_type
AND reportTechnology_Element.TechnologyTypeID = reportTechnologyTypes.TechnologyTypeID
AND reportGroup_Elements.element_id = reportElements.element_id
AND reportGroup_Elements.group_id = reportGroups.group_id
AND reportTechnologyTypes.TechnologyID = 'BlahHa'
AND reportGroups.name LIKE 'Hello%' )

HTH

-------------------------
What lies behind you and what lies ahead of you are small matters compared to what lies within you.-Ralph Waldo Emerson

Jeepaholic
Starting Member

36 Posts

Posted - 2002-07-14 : 12:06:23

Hi Nazim, thanks for the reply... I'm getting the following error with that statement:

Server: Msg 170, Level 15, State 1, Line 10
Line 10: Incorrect syntax near ')'.

Any thoughts?

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-07-14 : 12:11:10

Just need to alias the subquery:

select count(1)
from (
SELECT DISTINCT reportElements.element_id, reportElements.name
FROM reportElements, reportTechnologyTypes, reportTechnology_Element, reportGroup_Elements, reportGroups
WHERE reportElements.element_type = reportTechnology_Element.element_type
AND reportTechnology_Element.TechnologyTypeID = reportTechnologyTypes.TechnologyTypeID
AND reportGroup_Elements.element_id = reportElements.element_id
AND reportGroup_Elements.group_id = reportGroups.group_id
AND reportTechnologyTypes.TechnologyID = 'BlahHa'
AND reportGroups.name LIKE 'Hello%' ) AS A

Jeepaholic
Starting Member

36 Posts

Posted - 2002-07-14 : 12:15:10

Thanks guys, that definitely did the trick! Now to find some good reading material that matches the solution so I better understand it. <grin>

Thanks again...

Edited by - Jeepaholic on 07/14/2002 12:15:24

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources