Link to home
Start Free TrialLog in
Avatar of fusionfx2000
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(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.

Avatar of Lowfatspread
Lowfatspread
Flag of United Kingdom of Great Britain and Northern Ireland image
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..
Avatar of fusionfx2000
fusionfx2000

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.  
Avatar of Lowfatspread
Lowfatspread
Flag of United Kingdom of Great Britain and Northern Ireland image
does my query give you waht you want then ?

can you explain the scenario in business terms? to clarify..
Avatar of fusionfx2000
fusionfx2000

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.
Avatar of imran_fast
imran_fast
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

Avatar of fusionfx2000
fusionfx2000

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.
Avatar of imran_fast
imran_fast
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
Avatar of imran_fast
imran_fast
It seems you are working on sharepoint database.
Avatar of fusionfx2000
fusionfx2000

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'.
ASKER CERTIFIED SOLUTION
Avatar of imran_fast
imran_fast
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of fusionfx2000
fusionfx2000

ASKER

that works. thanks!