Solved

MS SQL Count

Posted on 2013-10-28
9
62 Views
Last Modified: 2016-07-10
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
0
Comment
Question by:beridius
9 Comments
 
LVL 24

Expert Comment

by:chaau
ID: 39607247
It is not really clear how " Number of Dupliactes" is calculated. Can you advise. Can you also provide sample data
0
 
LVL 48

Accepted Solution

by:
PortletPaul earned 500 total points
ID: 39607485
a difference I see in the upper/lower queries is the where clause, so I guess this is how you judge 'duplicates', and that is solved by using a case expression inside the count function, like this:
SELECT
        COUNT(*) AS 'Number of customers'
      , custSource_T.Name
      , COUNT(case when cust_T = 1 then cust_T.SourceID end) AS 'Number of duplicates'
      , 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

before I leave however I'm a little suspicious of your date range filter. I appears you want to locate everything for 2013-10-25, i.e. just that one day.

However, by using between you could get an incorrect answer, and to totally avoid this don't use between. This would ensure you only get 2013-10-25 data:
SELECT
        COUNT(*) AS 'Number of customers'
      , custSource_T.Name
      , COUNT(case when cust_T = 1 then cust_T.SourceID end) AS 'Number of duplicates'
      , cust_T.custClassID
FROM cust_T
        INNER JOIN custSource_T
                ON cust_T.SourceID = custSource_T.id
WHERE (cust_T.CrtdWhn >= CONVERT(datetime, '2013-10-25 00:00:00', 102) AND cust_T.CrtdWhn < 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

for more on this see: "Beware of Between"
0
 
LVL 2

Author Comment

by:beridius
ID: 39608211
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?
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 48

Expert Comment

by:PortletPaul
ID: 39608243
Yes, have you tried it yet?

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

Expert Comment

by:svalekar
ID: 39608480
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.
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 39610342
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)
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 41702015
https:#a39607485 provides an answer
0

Featured Post

Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Let's review the features of new SQL Server 2012 (Denali CTP3). It listed as below: PERCENT_RANK(): PERCENT_RANK() function will returns the percentage value of rank of the values among its group. PERCENT_RANK() function value always in be…
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Viewers will learn how the fundamental information of how to create a table.

828 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question