Count in SQL Server table

Question

I'm using the following SQL query:

select 
    FilteredSystemUser.systemuserid,
    FilteredPhoneCall.regardingobjectid 
from 
    FilteredPhoneCall 
Inner Join 
    FilteredSystemUser on FilteredSystemUser.systemuserid in (FilteredPhoneCall.createdby)
Inner Join 
    FilteredLead on FilteredLead.leadid = FilteredPhoneCall.regardingobjectid
where 
    DateDiff(d, FilteredPhoneCall.createdon, GETDATE()) = 0 
    and FilteredLead.statecode = 0 
    and FilteredLead.ownerid = FilteredSystemUser.systemuserid
    and FilteredPhoneCall.statecode = '1' 
    and FilteredPhoneCall.regardingobjecttypecode = 4
    and FilteredPhoneCall.createdby in ('c2dd1ddc-0374-e611-80dc-00155d3d1992', '53cfbe3a-a09a-e611-80df-00155dce24d0')

and get the following output

Here I have two entries in regardingid 97C29D24-BEE2-E611-80F1-00155DCE24EF,091B1CAB-C2E2-E611-80F1-00155DCE24EF against same system user.so i need to consider this as single entry and get the count other than this the both system users having another two ids.

I expect the above output

Put this search in a subquery and then group and count – shree.pat18 Jan 25 '17 at 10:35 — shree.pat18, Jan 25 '17 at 10:35
Use count(FilteredPhoneCall.regardingobjectid) – fizzi Jan 25 '17 at 10:38 — fizzi, Jan 25 '17 at 10:38

score 1 · Accepted Answer · edited May 23 '17 at 11:45

To get count of distinct values of a column reference here... SQL: Count distinct values from one column based on multiple criteria in other columns

For your case, try this:

   select 
        FilteredSystemUser.systemuserid,
        Count(DISTINCT FilteredPhoneCall.regardingobjectid ) as NoofCall
    from 
        FilteredPhoneCall 
    Inner Join 
        FilteredSystemUser on FilteredSystemUser.systemuserid in (FilteredPhoneCall.createdby)
    Inner Join 
        FilteredLead on FilteredLead.leadid = FilteredPhoneCall.regardingobjectid
    where 
        DateDiff(d, FilteredPhoneCall.createdon, GETDATE()) = 0 
        and FilteredLead.statecode = 0 
        and FilteredLead.ownerid = FilteredSystemUser.systemuserid
        and FilteredPhoneCall.statecode = '1' 
        and FilteredPhoneCall.regardingobjecttypecode = 4
        and FilteredPhoneCall.createdby in ('c2dd1ddc-0374-e611-80dc-00155d3d1992', '53cfbe3a-a09a-e611-80df-00155dce24d0')
    Group by FilteredSystemUser.systemuserid

PowerStar · Answer 2 · 2017-01-25T11:20:06.470

0

You can use count() and group by to achive this.

select      COUNT(DISTINCT FilteredPhoneCall.regardingobjectid ) as NoofCall
            ,FilteredSystemUser.systemuserid
from        FilteredPhoneCall 
inner Join  FilteredSystemUser
on          FilteredSystemUser.systemuserid in(FilteredPhoneCall.createdby)
Join        FilteredLead
on          FilteredLead.leadid = FilteredPhoneCall.regardingobjectid
where       DateDiff(d, FilteredPhoneCall.createdon, GETDATE()) = 0 
    and     FilteredLead.statecode = 0 and FilteredLead.ownerid = FilteredSystemUser.systemuserid
    and     FilteredPhoneCall.statecode = '1' and FilteredPhoneCall.regardingobjecttypecode = 4
    and     FilteredPhoneCall.createdby in ('c2dd1ddc-0374-e611-80dc-00155d3d1992','53cfbe3a-a09a-e611-80df-00155dce24d0')
    and     FilteredSystemUser.systemuserid in ('c2dd1ddc-0374-e611-80dc-00155d3d1992','53cfbe3a-a09a-e611-80df-00155dce24d0')
   GROUP BY FilteredSystemUser.systemuserid

edited Jan 25 '17 at 11:20

answered Jan 25 '17 at 10:40

PowerStar

893
5
15

If i use like this its shows 3 records for each system users. But I need to consider the same regadingid as single entry. – User Jan 25 '17 at 10:43
There is duplicate row was available in first four rows.so i need to ignore the duplicate – User Jan 25 '17 at 10:45
can you edit your question show the complete output you are looking for? – PowerStar Jan 25 '17 at 10:45

Satinder singh · Answer 3 · 2017-01-25T11:01:45.653

0

TRY THIS

;with CTE 
      AS (
    select 
        FilteredSystemUser.systemuserid,
        FilteredPhoneCall.regardingobjectid 
    from 
        FilteredPhoneCall 
    Inner Join 
        FilteredSystemUser on FilteredSystemUser.systemuserid in (FilteredPhoneCall.createdby)
    Inner Join 
        FilteredLead on FilteredLead.leadid = FilteredPhoneCall.regardingobjectid
    where 
        DateDiff(d, FilteredPhoneCall.createdon, GETDATE()) = 0 
        and FilteredLead.statecode = 0 
        and FilteredLead.ownerid = FilteredSystemUser.systemuserid
        and FilteredPhoneCall.statecode = '1' 
        and FilteredPhoneCall.regardingobjecttypecode = 4
        and FilteredPhoneCall.createdby in ('c2dd1ddc-0374-e611-80dc-00155d3d1992', '53cfbe3a-a09a-e611-80df-00155dce24d0') )

        select count(CTE.systemuserid) as myCOUNT,CTE.systemuserid,CTE.regardingobjectid from CTE
        group by  CTE.systemuserid,CTE.regardingobjectid

edited Jan 25 '17 at 11:01

answered Jan 25 '17 at 10:50

Satinder singh

10,100
16
60
102

Please go through my expected result. i need to collapse duplicate rows as 1 and then calculate the rows – User Jan 25 '17 at 10:54
@User Above query shows same result what you posted in OUTPUT IMAGE – Satinder singh Jan 25 '17 at 10:58
Here c2dd... system user id having 3 records and the 53cf... system user having 3 records. But there is some values are duplicates.So I need to take a single record in first set of duplicate and take another single record from the second set of duplicate. – User Jan 25 '17 at 11:10
The last two rows are having separate values so we need to take and count this. – User Jan 25 '17 at 11:12

Count in SQL Server table

3 Answers3