• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 315
  • Last Modified:

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(VARCHAR(8000), Data.ntext3), '<.*?>', '', 1, 1) AS ISSUE_DISCUSSION,
                      dbo.regexreplace(CONVERT(VARCHAR(8000), 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.UserData Data ON Data.tp_ListId = O.WOBJ_LIST_NAME LEFT OUTER JOIN
                      STS_wusvmepmapp01_1.dbo.UserInfo Owner ON Data.tp_SiteId = Owner.tp_SiteID AND Data.int4 = Owner.tp_ID LEFT OUTER JOIN
                      STS_wusvmepmapp01_1.dbo.UserInfo ResponsiblePerson ON Data.tp_SiteId = ResponsiblePerson.tp_SiteID AND
                      Data.int3 = ResponsiblePerson.tp_ID LEFT OUTER JOIN
                      STS_wusvmepmapp01_1.dbo.UserInfo Author ON Data.tp_SiteId = Author.tp_SiteID AND Data.tp_Author = Author.tp_ID LEFT OUTER JOIN
                      STS_wusvmepmapp01_1.dbo.UserInfo 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.

0
fusionfx2000
Asked:
fusionfx2000
  • 5
  • 4
  • 2
1 Solution
 
LowfatspreadCommented:
i believe this maybe it

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(*) 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(VARCHAR(8000), Data.ntext3), '<.*?>', '', 1, 1) AS ISSUE_DISCUSSION
             , dbo.regexreplace(CONVERT(VARCHAR(8000), Data.ntext4), '<.*?>', '', 1, 1) AS ISSUE_RESOLUTION
          From STS_wusvmepmapp01_1.dbo.UserData
         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.UserInfo Owner
    ON Data.tp_SiteId = Owner.tp_SiteID
   AND Data.OwnerTP_ID = Owner.tp_ID
  LEFT OUTER JOIN STS_wusvmepmapp01_1.dbo.UserInfo ResponsiblePerson
    ON Data.tp_SiteId = ResponsiblePerson.tp_SiteID
   AND Data.ResponsiblePersonTP_ID = ResponsiblePerson.tp_ID
  LEFT OUTER JOIN STS_wusvmepmapp01_1.dbo.UserInfo Author
    ON Data.tp_SiteId = Author.tp_SiteID
   AND Data.tp_Author = Author.tp_ID
  LEFT OUTER JOIN STS_wusvmepmapp01_1.dbo.UserInfo 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



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..
0
 
fusionfx2000Author Commented:
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.  
0
 
LowfatspreadCommented:
does my query give you waht you want then ?

can you explain the scenario in business terms? to clarify..
0
Transaction-level recovery for Oracle database

Veeam Explore for Oracle delivers low RTOs and RPOs with agentless transaction log backup and transaction-level recovery of Oracle databases. You can restore the database to a precise point in time, even to a specific transaction.

 
fusionfx2000Author Commented:
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.
0
 
imran_fastCommented:
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(VARCHAR(8000), Data.ntext3), '<.*?>', '', 1, 1) AS ISSUE_DISCUSSION
             , dbo.regexreplace(CONVERT(VARCHAR(8000), Data.ntext4), '<.*?>', '', 1, 1) AS ISSUE_RESOLUTION
          From STS_wusvmepmapp01_1.dbo.UserData
         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.UserInfo Owner
    ON Data.tp_SiteId = Owner.tp_SiteID
   AND Data.OwnerTP_ID = Owner.tp_ID
  LEFT OUTER JOIN STS_wusvmepmapp01_1.dbo.UserInfo ResponsiblePerson
    ON Data.tp_SiteId = ResponsiblePerson.tp_SiteID
   AND Data.ResponsiblePersonTP_ID = ResponsiblePerson.tp_ID
  LEFT OUTER JOIN STS_wusvmepmapp01_1.dbo.UserInfo Author
    ON Data.tp_SiteId = Author.tp_SiteID
   AND Data.tp_Author = Author.tp_ID
  LEFT OUTER JOIN STS_wusvmepmapp01_1.dbo.UserInfo 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

0
 
fusionfx2000Author Commented:
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.
0
 
imran_fastCommented:
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(VARCHAR(8000), ntext3), '<.*?>', '', 1, 1) AS ISSUE_DISCUSSION
             , dbo.regexreplace(CONVERT(VARCHAR(8000), ntext4), '<.*?>', '', 1, 1) AS ISSUE_RESOLUTION
          From STS_wusvmepmapp01_1.dbo.UserData
         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.UserInfo Owner
    ON Data.tp_SiteId = Owner.tp_SiteID
   AND Data.OwnerTP_ID = Owner.tp_ID
  LEFT OUTER JOIN STS_wusvmepmapp01_1.dbo.UserInfo ResponsiblePerson
    ON Data.tp_SiteId = ResponsiblePerson.tp_SiteID
   AND Data.ResponsiblePersonTP_ID = ResponsiblePerson.tp_ID
  LEFT OUTER JOIN STS_wusvmepmapp01_1.dbo.UserInfo Author
    ON Data.tp_SiteId = Author.tp_SiteID
   AND Data.tp_Author = Author.tp_ID
  LEFT OUTER JOIN STS_wusvmepmapp01_1.dbo.UserInfo 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
0
 
imran_fastCommented:
It seems you are working on sharepoint database.
0
 
fusionfx2000Author Commented:
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'.
0
 
imran_fastCommented:
try this
---------

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.ISSUE_NAME) 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 OwnerTP_ID
             , nvarchar1 AS ISSUE_STATUS
             , nvarchar2 AS ISSUE_CATEGORY
             , nvarchar3 AS ISSUE_NAME
             , nvarchar4 AS ISSUE_PRIORITY
             , dbo.regexreplace(CONVERT(VARCHAR(8000), ntext3), '<.*?>', '', 1, 1) AS ISSUE_DISCUSSION
             , dbo.regexreplace(CONVERT(VARCHAR(8000), ntext4), '<.*?>', '', 1, 1) AS ISSUE_RESOLUTION
          From STS_wusvmepmapp01_1.dbo.UserData
         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.UserInfo Owner
    ON Data.tp_SiteId = Owner.tp_SiteID
   AND Data.OwnerTP_ID = Owner.tp_ID
  LEFT OUTER JOIN STS_wusvmepmapp01_1.dbo.UserInfo ResponsiblePerson
    ON Data.tp_SiteId = ResponsiblePerson.tp_SiteID
   AND Data.ResponsiblePersonTP_ID = ResponsiblePerson.tp_ID
  LEFT OUTER JOIN STS_wusvmepmapp01_1.dbo.UserInfo Author
    ON Data.tp_SiteId = Author.tp_SiteID
   AND Data.tp_Author = Author.tp_ID
  LEFT OUTER JOIN STS_wusvmepmapp01_1.dbo.UserInfo 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
0
 
fusionfx2000Author Commented:
that works. thanks!
0

Featured Post

Microsoft Certification Exam 74-409

VeeamĀ® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

  • 5
  • 4
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now