Solved

MS SQL Count

Posted on 2013-10-28
9
39 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
Comment Utility
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
Comment Utility
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
Comment Utility
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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 48

Expert Comment

by:PortletPaul
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
https:#a39607485 provides an answer
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Performance is the key factor for any successful data integration project, knowing the type of transformation that you’re using is the first step on optimizing the SSIS flow performance, by utilizing the correct transformation or the design alternat…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

771 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now