Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 101
  • Last Modified:

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
0
beridius
Asked:
beridius
1 Solution
 
chaauCommented:
It is not really clear how " Number of Dupliactes" is calculated. Can you advise. Can you also provide sample data
0
 
PortletPaulCommented:
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
 
beridiusAuthor Commented:
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
NEW Veeam Backup for Microsoft Office 365 1.5

With Office 365, it’s your data and your responsibility to protect it. NEW Veeam Backup for Microsoft Office 365 eliminates the risk of losing access to your Office 365 data.

 
PortletPaulCommented:
Yes, have you tried it yet?

the count() function permits a case expression, use this instead of a completely new query.
0
 
svalekarCommented:
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
 
PortletPaulCommented:
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
 
PortletPaulCommented:
https:#a39607485 provides an answer
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now