asked on

MS SQL Count

I have a table I need to able to count the amount of customers came from a source

SELECT        COUNT(*) AS 'Number of customers', custSource_T.Name, cust_T.custClassID
FROM            cust_T INNER JOIN
                         custSource_T ON cust_T.SourceID = custSource_T.id
WHERE        (cust_T.CrtdWhn BETWEEN CONVERT(DATETIME, '2013-10-25 00:00:00', 102) AND CONVERT(DATETIME, '2013-10-26 00:00:00', 102)) AND 
                         (cust_T.custClassID = 3)
GROUP BY custSource_T.sName, cust_T.custClassID

Open in new window

which display like

number of customers Name
50 google
60 Bing

but I want to able to

SELECT
(SELECT        COUNT(*) AS 'Number of customers', custSource_T.Name, cust_T.custClassID
FROM            cust_T INNER JOIN
                         custSource_T ON cust_T.SourceID = custSource_T.id
WHERE        (cust_T.CrtdWhn BETWEEN CONVERT(DATETIME, '2013-10-25 00:00:00', 102) AND CONVERT(DATETIME, '2013-10-26 00:00:00', 102)) AND 
                         (cust_T.custClassID = 3)
GROUP BY custSource_T.sName, cust_T.custClassID
)AS number of customers,

(SELECT        COUNT(*) AS 'Number of dupliactes', custSource_T.Name, cust_T.custClassID
FROM            cust_T INNER JOIN
                         custSource_T ON cust_T.SourceID = custSource_T.id
WHERE        (cust_T.CrtdWhn BETWEEN CONVERT(DATETIME, '2013-10-25 00:00:00', 102) AND CONVERT(DATETIME, '2013-10-26 00:00:00', 102)) AND 
                         (cust_T.custClassID = 3) AND (cust_T = 1)
GROUP BY custSource_T.sName, cust_T.custClassID)
)

Open in new window

for this to display

number of customers Name Number of Dupliactes
50 google 4
60 Bing 0

chaau

It is not really clear how " Number of Dupliactes" is calculated. Can you advise. Can you also provide sample data

ASKER CERTIFIED SOLUTION

PortletPaul

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

beridius

ASKER

you are right

I have 2 select statement the only difference is in the where cause
I need to be able to count(*) on each column how would I do that?

PortletPaul

Yes, have you tried it yet?

the count() function permits a case expression, use this instead of a completely new query.

Satish Valekar

Please post table structure and some data.

I think with Dense_Rank function u can get duplicate values.
Take max of dense_rank column, you will get duplicate count.

PortletPaul

I don't see how dense_rank is relevant when a count has been asked for.
Ranking and counting are quite different.

the "duplicate" calculation is determined by a field value of 1:

count(case when cust_T = 1 then cust_T.SourceID end)

see the second query of the question, second where clause (lines 13 & 14)

PortletPaul

https:#a39607485 provides an answer