fusionfx2000
asked on
Count inside of select statement
I have a query where I want to count the number of particular statuses. Here is the query:
SELECT DISTINCT
P.PROJ_NAME, Data.nvarchar1 AS ISSUE_STATUS, Data.nvarchar2 AS ISSUE_CATEGORY, Data.nvarchar3 AS ISSUE_NAME,
Data.nvarchar4 AS ISSUE_PRIORITY, dbo.regexreplace(CONVERT(V ARCHAR(800 0), Data.ntext3), '<.*?>', '', 1, 1) AS ISSUE_DISCUSSION,
dbo.regexreplace(CONVERT(V ARCHAR(800 0), Data.ntext4), '<.*?>', '', 1, 1) AS ISSUE_RESOLUTION, Owner.tp_Title AS Owner,
ResponsiblePerson.tp_Title AS AssignedTo, Author.tp_Title AS Author, Editor.tp_Title AS Editor, Data.tp_IsCurrent
FROM MSP_WEB_PROJECTS P INNER JOIN
MSP_WEB_OBJECTS O ON P.WPROJ_ISSUE_LIST_NAME = O.WOBJ_LIST_NAME LEFT OUTER JOIN
STS_wusvmepmapp01_1.dbo.Us erData Data ON Data.tp_ListId = O.WOBJ_LIST_NAME LEFT OUTER JOIN
STS_wusvmepmapp01_1.dbo.Us erInfo Owner ON Data.tp_SiteId = Owner.tp_SiteID AND Data.int4 = Owner.tp_ID LEFT OUTER JOIN
STS_wusvmepmapp01_1.dbo.Us erInfo ResponsiblePerson ON Data.tp_SiteId = ResponsiblePerson.tp_SiteI D AND
Data.int3 = ResponsiblePerson.tp_ID LEFT OUTER JOIN
STS_wusvmepmapp01_1.dbo.Us erInfo Author ON Data.tp_SiteId = Author.tp_SiteID AND Data.tp_Author = Author.tp_ID LEFT OUTER JOIN
STS_wusvmepmapp01_1.dbo.Us erInfo Editor ON Data.tp_SiteId = Editor.tp_SiteID AND Data.tp_Editor = Editor.tp_ID
WHERE (Data.nvarchar3 IS NOT NULL) and (Data.tp_IsCurrent = 1)
I want to add the following ...............Count(data. nvarchar3) as ActiveIssues....where data.nvarchar1 = '(1) Active'
when I try to add this it says something about aggregate functions and group by. Please help...I need to retain the distinct.
thanks.
SELECT DISTINCT
P.PROJ_NAME, Data.nvarchar1 AS ISSUE_STATUS, Data.nvarchar2 AS ISSUE_CATEGORY, Data.nvarchar3 AS ISSUE_NAME,
Data.nvarchar4 AS ISSUE_PRIORITY, dbo.regexreplace(CONVERT(V
dbo.regexreplace(CONVERT(V
ResponsiblePerson.tp_Title
FROM MSP_WEB_PROJECTS P INNER JOIN
MSP_WEB_OBJECTS O ON P.WPROJ_ISSUE_LIST_NAME = O.WOBJ_LIST_NAME LEFT OUTER JOIN
STS_wusvmepmapp01_1.dbo.Us
STS_wusvmepmapp01_1.dbo.Us
STS_wusvmepmapp01_1.dbo.Us
Data.int3 = ResponsiblePerson.tp_ID LEFT OUTER JOIN
STS_wusvmepmapp01_1.dbo.Us
STS_wusvmepmapp01_1.dbo.Us
WHERE (Data.nvarchar3 IS NOT NULL) and (Data.tp_IsCurrent = 1)
I want to add the following ...............Count(data.
when I try to add this it says something about aggregate functions and group by. Please help...I need to retain the distinct.
thanks.
ASKER
I need the distinct for the initial query excluding the (Count(data.nvarchar3). If I don't use distinct, (Count(data.nvarchar3) will return 3 and this isn't what I want. I want (Count(data.nvarchar3) to return 1 since the distinct query retrieves the exact records I need to do the count on the issue status.
does my query give you waht you want then ?
can you explain the scenario in business terms? to clarify..
can you explain the scenario in business terms? to clarify..
ASKER
No. you're query doesn't work because of the reason I listed before your last post. I need to select distinct records first and then do the count for only the active issues '(1) Active'. Data.nvarchar1 contains this value.
hi ,
Try this post
SELECT
P.PROJ_NAME
, ISSUE_STATUS
, ISSUE_CATEGORY
, ISSUE_NAME
, ISSUE_PRIORITY
, ISSUE_DISCUSSION
, ISSUE_RESOLUTION
, Owner.tp_Title AS Owner
, ResponsiblePerson.tp_Title AS AssignedTo
, Author.tp_Title AS Author
, Editor.tp_Title AS Editor
, 1 as tp_IsCurrent
, Count(distinct data.nvarchar3) as Issues
FROM MSP_WEB_PROJECTS P
INNER JOIN MSP_WEB_OBJECTS O
ON P.WPROJ_ISSUE_LIST_NAME = O.WOBJ_LIST_NAME
LEFT OUTER JOIN
(Select TP_listID
, TP_Siteid
, TP_Author
, TP_Editor
, int3 as ResponsiblePersonTP_ID
, int4 as ONWERTP_ID
, nvarchar1 AS ISSUE_STATUS
, nvarchar2 AS ISSUE_CATEGORY
, nvarchar3 AS ISSUE_NAME
, nvarchar4 AS ISSUE_PRIORITY
, dbo.regexreplace(CONVERT(V ARCHAR(800 0), Data.ntext3), '<.*?>', '', 1, 1) AS ISSUE_DISCUSSION
, dbo.regexreplace(CONVERT(V ARCHAR(800 0), Data.ntext4), '<.*?>', '', 1, 1) AS ISSUE_RESOLUTION
From STS_wusvmepmapp01_1.dbo.Us erData
Where TP_isCurrent = 1
and nvarchar3 IS NOT NULL and nvarchar1 = 1
) as Data
ON Data.tp_ListId = O.WOBJ_LIST_NAME
LEFT OUTER JOIN STS_wusvmepmapp01_1.dbo.Us erInfo Owner
ON Data.tp_SiteId = Owner.tp_SiteID
AND Data.OwnerTP_ID = Owner.tp_ID
LEFT OUTER JOIN STS_wusvmepmapp01_1.dbo.Us erInfo ResponsiblePerson
ON Data.tp_SiteId = ResponsiblePerson.tp_SiteI D
AND Data.ResponsiblePersonTP_I D = ResponsiblePerson.tp_ID
LEFT OUTER JOIN STS_wusvmepmapp01_1.dbo.Us erInfo Author
ON Data.tp_SiteId = Author.tp_SiteID
AND Data.tp_Author = Author.tp_ID
LEFT OUTER JOIN STS_wusvmepmapp01_1.dbo.Us erInfo Editor
ON Data.tp_SiteId = Editor.tp_SiteID
AND Data.tp_Editor = Editor.tp_ID
Group by
P.PROJ_NAME
, ISSUE_STATUS
, ISSUE_CATEGORY
, ISSUE_NAME
, ISSUE_PRIORITY
, ISSUE_DISCUSSION
, ISSUE_RESOLUTION
, Owner.tp_Title
, ResponsiblePerson.tp_Title
, Author.tp_Title
, Editor.tp_Title
Try this post
SELECT
P.PROJ_NAME
, ISSUE_STATUS
, ISSUE_CATEGORY
, ISSUE_NAME
, ISSUE_PRIORITY
, ISSUE_DISCUSSION
, ISSUE_RESOLUTION
, Owner.tp_Title AS Owner
, ResponsiblePerson.tp_Title
, Author.tp_Title AS Author
, Editor.tp_Title AS Editor
, 1 as tp_IsCurrent
, Count(distinct data.nvarchar3) as Issues
FROM MSP_WEB_PROJECTS P
INNER JOIN MSP_WEB_OBJECTS O
ON P.WPROJ_ISSUE_LIST_NAME = O.WOBJ_LIST_NAME
LEFT OUTER JOIN
(Select TP_listID
, TP_Siteid
, TP_Author
, TP_Editor
, int3 as ResponsiblePersonTP_ID
, int4 as ONWERTP_ID
, nvarchar1 AS ISSUE_STATUS
, nvarchar2 AS ISSUE_CATEGORY
, nvarchar3 AS ISSUE_NAME
, nvarchar4 AS ISSUE_PRIORITY
, dbo.regexreplace(CONVERT(V
, dbo.regexreplace(CONVERT(V
From STS_wusvmepmapp01_1.dbo.Us
Where TP_isCurrent = 1
and nvarchar3 IS NOT NULL and nvarchar1 = 1
) as Data
ON Data.tp_ListId = O.WOBJ_LIST_NAME
LEFT OUTER JOIN STS_wusvmepmapp01_1.dbo.Us
ON Data.tp_SiteId = Owner.tp_SiteID
AND Data.OwnerTP_ID = Owner.tp_ID
LEFT OUTER JOIN STS_wusvmepmapp01_1.dbo.Us
ON Data.tp_SiteId = ResponsiblePerson.tp_SiteI
AND Data.ResponsiblePersonTP_I
LEFT OUTER JOIN STS_wusvmepmapp01_1.dbo.Us
ON Data.tp_SiteId = Author.tp_SiteID
AND Data.tp_Author = Author.tp_ID
LEFT OUTER JOIN STS_wusvmepmapp01_1.dbo.Us
ON Data.tp_SiteId = Editor.tp_SiteID
AND Data.tp_Editor = Editor.tp_ID
Group by
P.PROJ_NAME
, ISSUE_STATUS
, ISSUE_CATEGORY
, ISSUE_NAME
, ISSUE_PRIORITY
, ISSUE_DISCUSSION
, ISSUE_RESOLUTION
, Owner.tp_Title
, ResponsiblePerson.tp_Title
, Author.tp_Title
, Editor.tp_Title
ASKER
I'm getting the following:
Server: Msg 107, Level 16, State 2, Line 1
The column prefix 'Data' does not match with a table name or alias name used in the query.
Server: Msg 107, Level 16, State 1, Line 1
The column prefix 'Data' does not match with a table name or alias name used in the query.
Server: Msg 107, Level 16, State 2, Line 1
The column prefix 'Data' does not match with a table name or alias name used in the query.
Server: Msg 107, Level 16, State 1, Line 1
The column prefix 'Data' does not match with a table name or alias name used in the query.
hmmm. try this one
SELECT
P.PROJ_NAME
, ISSUE_STATUS
, ISSUE_CATEGORY
, ISSUE_NAME
, ISSUE_PRIORITY
, ISSUE_DISCUSSION
, ISSUE_RESOLUTION
, Owner.tp_Title AS Owner
, ResponsiblePerson.tp_Title AS AssignedTo
, Author.tp_Title AS Author
, Editor.tp_Title AS Editor
, 1 as tp_IsCurrent
, Count(distinct data.nvarchar3) as Issues
FROM MSP_WEB_PROJECTS P
INNER JOIN MSP_WEB_OBJECTS O
ON P.WPROJ_ISSUE_LIST_NAME = O.WOBJ_LIST_NAME
LEFT OUTER JOIN
(Select TP_listID
, TP_Siteid
, TP_Author
, TP_Editor
, int3 as ResponsiblePersonTP_ID
, int4 as ONWERTP_ID
, nvarchar1 AS ISSUE_STATUS
, nvarchar2 AS ISSUE_CATEGORY
, nvarchar3 AS ISSUE_NAME
, nvarchar4 AS ISSUE_PRIORITY
, dbo.regexreplace(CONVERT(V ARCHAR(800 0), ntext3), '<.*?>', '', 1, 1) AS ISSUE_DISCUSSION
, dbo.regexreplace(CONVERT(V ARCHAR(800 0), ntext4), '<.*?>', '', 1, 1) AS ISSUE_RESOLUTION
From STS_wusvmepmapp01_1.dbo.Us erData
Where TP_isCurrent = 1
and nvarchar3 IS NOT NULL and nvarchar1 = 1
) as Data
ON Data.tp_ListId = O.WOBJ_LIST_NAME
LEFT OUTER JOIN STS_wusvmepmapp01_1.dbo.Us erInfo Owner
ON Data.tp_SiteId = Owner.tp_SiteID
AND Data.OwnerTP_ID = Owner.tp_ID
LEFT OUTER JOIN STS_wusvmepmapp01_1.dbo.Us erInfo ResponsiblePerson
ON Data.tp_SiteId = ResponsiblePerson.tp_SiteI D
AND Data.ResponsiblePersonTP_I D = ResponsiblePerson.tp_ID
LEFT OUTER JOIN STS_wusvmepmapp01_1.dbo.Us erInfo Author
ON Data.tp_SiteId = Author.tp_SiteID
AND Data.tp_Author = Author.tp_ID
LEFT OUTER JOIN STS_wusvmepmapp01_1.dbo.Us erInfo Editor
ON Data.tp_SiteId = Editor.tp_SiteID
AND Data.tp_Editor = Editor.tp_ID
Group by
P.PROJ_NAME
, ISSUE_STATUS
, ISSUE_CATEGORY
, ISSUE_NAME
, ISSUE_PRIORITY
, ISSUE_DISCUSSION
, ISSUE_RESOLUTION
, Owner.tp_Title
, ResponsiblePerson.tp_Title
, Author.tp_Title
, Editor.tp_Title
SELECT
P.PROJ_NAME
, ISSUE_STATUS
, ISSUE_CATEGORY
, ISSUE_NAME
, ISSUE_PRIORITY
, ISSUE_DISCUSSION
, ISSUE_RESOLUTION
, Owner.tp_Title AS Owner
, ResponsiblePerson.tp_Title
, Author.tp_Title AS Author
, Editor.tp_Title AS Editor
, 1 as tp_IsCurrent
, Count(distinct data.nvarchar3) as Issues
FROM MSP_WEB_PROJECTS P
INNER JOIN MSP_WEB_OBJECTS O
ON P.WPROJ_ISSUE_LIST_NAME = O.WOBJ_LIST_NAME
LEFT OUTER JOIN
(Select TP_listID
, TP_Siteid
, TP_Author
, TP_Editor
, int3 as ResponsiblePersonTP_ID
, int4 as ONWERTP_ID
, nvarchar1 AS ISSUE_STATUS
, nvarchar2 AS ISSUE_CATEGORY
, nvarchar3 AS ISSUE_NAME
, nvarchar4 AS ISSUE_PRIORITY
, dbo.regexreplace(CONVERT(V
, dbo.regexreplace(CONVERT(V
From STS_wusvmepmapp01_1.dbo.Us
Where TP_isCurrent = 1
and nvarchar3 IS NOT NULL and nvarchar1 = 1
) as Data
ON Data.tp_ListId = O.WOBJ_LIST_NAME
LEFT OUTER JOIN STS_wusvmepmapp01_1.dbo.Us
ON Data.tp_SiteId = Owner.tp_SiteID
AND Data.OwnerTP_ID = Owner.tp_ID
LEFT OUTER JOIN STS_wusvmepmapp01_1.dbo.Us
ON Data.tp_SiteId = ResponsiblePerson.tp_SiteI
AND Data.ResponsiblePersonTP_I
LEFT OUTER JOIN STS_wusvmepmapp01_1.dbo.Us
ON Data.tp_SiteId = Author.tp_SiteID
AND Data.tp_Author = Author.tp_ID
LEFT OUTER JOIN STS_wusvmepmapp01_1.dbo.Us
ON Data.tp_SiteId = Editor.tp_SiteID
AND Data.tp_Editor = Editor.tp_ID
Group by
P.PROJ_NAME
, ISSUE_STATUS
, ISSUE_CATEGORY
, ISSUE_NAME
, ISSUE_PRIORITY
, ISSUE_DISCUSSION
, ISSUE_RESOLUTION
, Owner.tp_Title
, ResponsiblePerson.tp_Title
, Author.tp_Title
, Editor.tp_Title
It seems you are working on sharepoint database.
ASKER
Now I'm getting this:
Server: Msg 207, Level 16, State 3, Line 1
Invalid column name 'OWNERTP_ID'.
Server: Msg 207, Level 16, State 1, Line 1
Invalid column name 'nvarchar3'.
Server: Msg 207, Level 16, State 3, Line 1
Invalid column name 'OWNERTP_ID'.
Server: Msg 207, Level 16, State 1, Line 1
Invalid column name 'nvarchar3'.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
that works. thanks!
SELECT
P.PROJ_NAME
, ISSUE_STATUS
, ISSUE_CATEGORY
, ISSUE_NAME
, ISSUE_PRIORITY
, ISSUE_DISCUSSION
, ISSUE_RESOLUTION
, Owner.tp_Title AS Owner
, ResponsiblePerson.tp_Title
, Author.tp_Title AS Author
, Editor.tp_Title AS Editor
, 1 as tp_IsCurrent
, Count(*) as Issues
FROM MSP_WEB_PROJECTS P
INNER JOIN MSP_WEB_OBJECTS O
ON P.WPROJ_ISSUE_LIST_NAME = O.WOBJ_LIST_NAME
LEFT OUTER JOIN
(Select TP_listID
, TP_Siteid
, TP_Author
, TP_Editor
, int3 as ResponsiblePersonTP_ID
, int4 as ONWERTP_ID
, nvarchar1 AS ISSUE_STATUS
, nvarchar2 AS ISSUE_CATEGORY
, nvarchar3 AS ISSUE_NAME
, nvarchar4 AS ISSUE_PRIORITY
, dbo.regexreplace(CONVERT(V
, dbo.regexreplace(CONVERT(V
From STS_wusvmepmapp01_1.dbo.Us
Where TP_isCurrent = 1
and nvarchar3 IS NOT NULL
) as Data
ON Data.tp_ListId = O.WOBJ_LIST_NAME
LEFT OUTER JOIN STS_wusvmepmapp01_1.dbo.Us
ON Data.tp_SiteId = Owner.tp_SiteID
AND Data.OwnerTP_ID = Owner.tp_ID
LEFT OUTER JOIN STS_wusvmepmapp01_1.dbo.Us
ON Data.tp_SiteId = ResponsiblePerson.tp_SiteI
AND Data.ResponsiblePersonTP_I
LEFT OUTER JOIN STS_wusvmepmapp01_1.dbo.Us
ON Data.tp_SiteId = Author.tp_SiteID
AND Data.tp_Author = Author.tp_ID
LEFT OUTER JOIN STS_wusvmepmapp01_1.dbo.Us
ON Data.tp_SiteId = Editor.tp_SiteID
AND Data.tp_Editor = Editor.tp_ID
Group by
P.PROJ_NAME
, ISSUE_STATUS
, ISSUE_CATEGORY
, ISSUE_NAME
, ISSUE_PRIORITY
, ISSUE_DISCUSSION
, ISSUE_RESOLUTION
, Owner.tp_Title
, ResponsiblePerson.tp_Title
, Author.tp_Title
, Editor.tp_Title
you don't need a distinct if you are counting....
but Its unclear what you wish to count....
since you still appear to want to have all issues listed..