• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 303
  • Last Modified:

SQL Count Query

When I use the query below I get what I want i.e.

TitleType TotCount
including paper DD      0

but as soon as I add in c.TPRgroup I get back nothing i.e.
TitleType TotCount

I would like to see
TitleType                       TPRGroup                   TotCount
including paper DD        either "NULL" or " "    0

Many thanks in advance.
SELECT     
	'including paper DD' AS TitleType,
	--c.TPRGroup, 
	case when count(c.customerid) = 0  then 0 else Count(c.customerid) end As TotCount
FROM         Financials f INNER JOIN
                      Customers c ON f.CustomerID = c.CustomerID
WHERE     (LEFT(c.CustomerID, 6) = 'wvc112') AND ((f.ResultCode LIKE 'DDD%') or (f.ResultCode LIKE 'DUPDD%'))
--GROUP BY c.tprgroup

Open in new window

0
ITgirl
Asked:
ITgirl
  • 4
1 Solution
 
Kevin CrossChief Technology OfficerCommented:
Don't think you need the CASE statement, but not sure why it would make the query break but for giggles go ahead and try like this:
SELECT 'including paper DD' AS TitleType, c.TPRGroup
     , COUNT(c.customerid) As TotCount
FROM Financials f 
INNER JOIN Customers c ON f.CustomerID = c.CustomerID
WHERE (LEFT(c.CustomerID, 6) = 'wvc112') AND ((f.ResultCode LIKE 'DDD%') or (f.ResultCode LIKE 'DUPDD%'))
GROUP BY c.tprgroup

Open in new window

0
 
ITgirlAuthor Commented:
mwvisa1 - I already had this, and if you have a look at my question it returns nothing at all!

Thanks for your input.
0
 
Kevin CrossChief Technology OfficerCommented:
Ah, and the issue is probably you don't have any rows that meet the criteria -- try using a conditional aggregate instead.
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
Kevin CrossChief Technology OfficerCommented:
SELECT 'including paper DD' AS TitleType
     , c.TPRGroup
     , COUNT(CASE WHEN (LEFT(c.CustomerID, 6) = 'wvc112') AND ((f.ResultCode LIKE 'DDD%') or (f.ResultCode LIKE 'DUPDD%')) THEN c.customerid END) As TotCount
FROM Financials f 
INNER JOIN Customers c ON f.CustomerID = c.CustomerID
GROUP BY c.tprgroup

Open in new window


You will get a row for every TPRGroup within your data.  If that works, you can try combinations that fit your needs better like maybe having the CustomerID filter in the WHERE and the ResultCode in the conditional aggregate.

e.g.,
SELECT 'including paper DD' AS TitleType
     , c.TPRGroup
     , COUNT(CASE WHEN ((f.ResultCode LIKE 'DDD%') or (f.ResultCode LIKE 'DUPDD%')) THEN c.customerid END) As TotCount
FROM Financials f 
INNER JOIN Customers c ON f.CustomerID = c.CustomerID
WHERE (LEFT(c.CustomerID, 6) = 'wvc112')
GROUP BY c.tprgroup

Open in new window

0
 
Alpesh PatelAssistant ConsultantCommented:
SELECT    
        'including paper DD' AS TitleType,
        c.TPRGroup,
        case when count(c.customerid) = 0  then 0 else Count(c.customerid) end As TotCount
FROM         Financials f Left Outer JOIN
                      Customers c ON f.CustomerID = c.CustomerID
WHERE     (LEFT(c.CustomerID, 6) = 'wvc112') AND ((f.ResultCode LIKE 'DDD%') or (f.ResultCode LIKE 'DUPDD%'))
GROUP BY c.tprgroup
0
 
Kevin CrossChief Technology OfficerCommented:
If the second scenario I pointed out in the accepted post is true, i.e., your filter on Customers will work in the WHERE clause and it is the filter on Financials table that is causing the no results, then you can try the alternative eluded to by PatelAlpesh's last post --- though, you need to flip the order of the tables.

 SELECT 'including paper DD' AS TitleType
     , c.TPRGroup
     , COUNT(f.CustomerID) As TotCount
FROM Customers c
LEFT OUTER JOIN Financials f ON f.CustomerID = c.CustomerID
   AND ((f.ResultCode LIKE 'DDD%') or (f.ResultCode LIKE 'DUPDD%'))
WHERE (LEFT(c.CustomerID, 6) = 'wvc112')
GROUP BY c.tprgroup

Note the highlighted changes.  You need to have Customers table be the focus and then put all the filtering of Financials table in the JOIN clause; if you keep conditions in WHERE clause for the OUTER JOIN'd table, then it will basically act like an INNER JOIN anyway.

Since doing an OUTER JOIN means, you will get all rows of Customers that satisfy the WHERE condition then counting c.CustomerID will give you count of customers; conversely, since you may or may not have a match in Financials table so f.CustomerID may be null causing count on that field to be a little more telling/appropriate.

Hope that helps give another option.  

Anyway, glad I could help you and thanks for the points.

Best regards and happy coding,

Kevin
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

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