?
Solved

MS SQL Count

Posted on 2013-10-28
9
Medium Priority
?
83 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
9 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 49

Accepted Solution

by:
PortletPaul 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
Learn how to optimize MySQL for your business need

With the increasing importance of apps & networks in both business & personal interconnections, perfor. has become one of the key metrics of successful communication. This ebook is a hands-on business-case-driven guide to understanding MySQL query parameter tuning & database perf

 
LVL 49

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 49

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 49

Expert Comment

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

Featured Post

NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

Question has a verified solution.

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

International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Suggested Courses

770 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