Improve company productivity with a Business Account.Sign Up

x
?
Solved

MS SQL Count

Posted on 2013-10-28
9
Medium Priority
?
111 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
7 Comments
 
LVL 25

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 50

Accepted Solution

by:
Paul earned 2000 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
What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

 
LVL 50

Expert Comment

by:Paul
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 50

Expert Comment

by:Paul
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 50

Expert Comment

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

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
This month, Experts Exchange sat down with resident SQL expert, Jim Horn, for an in-depth look into the makings of a successful career in SQL.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

606 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