?
Solved

SQL COUNT 2 TABLES INCLUDE ZERO

Posted on 2012-09-12
8
Medium Priority
?
583 Views
Last Modified: 2012-09-13
Hi Experts,

I have two tables with the exact same column (referral) and I'm trying to get a count off both while including zero values.

Here is what I have so far:

SELECT z.Referral, SUM(z.total1) total2

FROM

(SELECT w.Referral, COUNT(w.Referral) total1
FROM tblwebforms w
LEFT OUTER JOIN tblautoreplies ar ON ar.referral = w.referral 
AND CONVERT(varchar, w.DateSubmitted, 101) 
BETWEEN CONVERT(varchar, '09/12/2012', 101) AND CONVERT(varchar, '09/12/2012', 101) 
GROUP BY w.Referral

UNION ALL

SELECT ar.Referral, COUNT(ar.Referral) total1
FROM tblautoreplies ar
LEFT OUTER JOIN tblwebforms w ON w.referral = ar.referral 
AND CONVERT(varchar, ar.DateSent, 101) 
BETWEEN CONVERT(varchar, '09/12/2012', 101) AND CONVERT(varchar, '09/12/2012', 101) 
GROUP BY ar.Referral) z

WHERE z.Referral != 'ANDROID'
GROUP BY z.Referral

Open in new window


The above gives me outrageous totals.

If I try it without the LEFT OUTR JOINS like:

SELECT z.Referral, SUM(z.total1) total2

FROM

(SELECT w.Referral, COUNT(w.Referral) total1
FROM tblwebforms w
--LEFT OUTER JOIN tblautoreplies ar ON ar.referral = w.referral 
WHERE CONVERT(varchar, w.DateSubmitted, 101) 
BETWEEN CONVERT(varchar, '09/12/2012', 101) AND CONVERT(varchar, '09/12/2012', 101) 
GROUP BY w.Referral

UNION ALL

SELECT ar.Referral, COUNT(ar.Referral) total1
FROM tblautoreplies ar
--LEFT OUTER JOIN tblwebforms w ON w.referral = ar.referral 
WHERE CONVERT(varchar, ar.DateSent, 101) 
BETWEEN CONVERT(varchar, '09/12/2012', 101) AND CONVERT(varchar, '09/12/2012', 101) 
GROUP BY ar.Referral) z

WHERE z.Referral != 'ANDROID'
GROUP BY z.Referral

Open in new window


I only get counts greater than 0.

Any suggestions?
0
Comment
Question by:rmartes
  • 3
  • 3
  • 2
8 Comments
 
LVL 22

Expert Comment

by:Thomasian
ID: 38391681
SELECT Referral = COALESCE(w.Referral, ar.Referral), COUNT(*) total
FROM tblwebforms w
FULL JOIN tblautoreplies ar ON ar.referral = w.referral 
AND CONVERT(varchar, w.DateSubmitted, 101) 
BETWEEN CONVERT(varchar, '09/12/2012', 101) AND CONVERT(varchar, '09/12/2012', 101) 
AND CONVERT(varchar, ar.DateSent, 101) 
BETWEEN CONVERT(varchar, '09/12/2012', 101) AND CONVERT(varchar, '09/12/2012', 101) 
GROUP BY COALESCE(w.Referral, ar.Referral)

Open in new window

0
 
LVL 22

Expert Comment

by:Thomasian
ID: 38391687
Sorry, that should be OR instead of AND
SELECT Referral = COALESCE(w.Referral, ar.Referral), COUNT(*) total
FROM tblwebforms w
FULL JOIN tblautoreplies ar ON ar.referral = w.referral 
AND
(
CONVERT(varchar, w.DateSubmitted, 101) 
BETWEEN CONVERT(varchar, '09/12/2012', 101) AND CONVERT(varchar, '09/12/2012', 101) 

OR

CONVERT(varchar, ar.DateSent, 101) 
BETWEEN CONVERT(varchar, '09/12/2012', 101) AND CONVERT(varchar, '09/12/2012', 101) 
)
GROUP BY COALESCE(w.Referral, ar.Referral)

Open in new window

0
 

Author Comment

by:rmartes
ID: 38391736
Thanks for the reply, but its not working.

For 09/12/2012, I am suppose to be getting the following:

Google|0
iPhone|6
iPad|0

With your query, I am getting:

Google|207
iPhone|1580
iPad|10
0
NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

 
LVL 70

Expert Comment

by:Scott Pletcher
ID: 38392349
SELECT z.Referral, SUM(z.total1) total2

FROM

(SELECT w.Referral, COUNT(w.Referral) total1
FROM tblwebforms w
WHERE w.DateSubmitted >= '20120912'
  AND w.DateSubmitted < '20120913'
  AND w.Referral != 'ANDROID'
GROUP BY w.Referral

UNION ALL

SELECT ar.Referral, COUNT(ar.Referral) total1
FROM tblautoreplies ar
WHERE ar.DateSubmitted >= '20120912'
  AND ar.DateSubmitted < '20120913'
  AND ar.Referral != 'ANDROID'
GROUP BY ar.Referral) AS z

WHERE z.Referral != 'ANDROID'
GROUP BY z.Referral
0
 

Author Comment

by:rmartes
ID: 38392914
Thanks, but no luck. I'm still getting results not including zeros.
0
 
LVL 70

Accepted Solution

by:
Scott Pletcher earned 2000 total points
ID: 38392927
SELECT
    r.Referral, SUM(ISNULL(z.total1, 0)) total2
FROM (
    SELECT DISTINCT Referral
    FROM tblwebforms
    UNION
    SELECT DISTINCT Referral
    FROM tblautoreplies
) AS r
LEFT OUTER JOIN (
    SELECT w.Referral, COUNT(w.Referral) total1
    FROM tblwebforms w
    WHERE
               w.DateSubmitted >= '20120912'
        AND w.DateSubmitted < '20120913'
        AND w.Referral != 'ANDROID'
    GROUP BY w.Referral
    UNION ALL
    SELECT ar.Referral, COUNT(ar.Referral) total1
    FROM tblautoreplies ar
    WHERE
               ar.DateSubmitted >= '20120912'
        AND ar.DateSubmitted < '20120913'
        AND ar.Referral != 'ANDROID'
    GROUP BY ar.Referral
) AS z ON
    z.referral = r.referral
WHERE
    r.Referral != 'ANDROID'
GROUP BY
    r.Referral
0
 
LVL 22

Expert Comment

by:Thomasian
ID: 38393357
The code from ScottPletcher should work. The first derived table (aliased 'r') is used to create a complete list of Referrals from both tables. But if you have a table (tblReferrals?) which stores a list of unique referrals, it is recommended that you use that instead.

Here's another approach using CROSS APPLY.
SELECT
	r.Referral
	, Total = w.Total + ar.Total
FROM
	tblReferrals r CROSS APPLY
	(SELECT COUNT(*) Total
		FROM tblwebforms
		WHERE r.Referral = Referral
			AND DateSubmitted >= '2012-09-12'
			AND DateSubmitted < '2012-09-13'
	) w CROSS APPLY
	(SELECT COUNT(*) Total
		FROM tblautoreplies
		WHERE r.Referral = Referral
			AND DateSubmitted >= '2012-09-12'
			AND DateSubmitted < '2012-09-13'
	) ar
WHERE r.Referral != 'ANDROID'

Open in new window

0
 

Author Closing Comment

by:rmartes
ID: 38395925
Excellent!
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

This article will describe one method to parse a delimited string into a table of data.   Why would I do that you ask?  Let's say that you need to pass multiple parameters into a stored procedure to search for.  For our sake, we'll say that we wa…
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
this video summaries big data hadoop online training demo (http://onlineitguru.com/big-data-hadoop-online-training-placement.html) , and covers basics in big data hadoop .
Screencast - Getting to Know the Pipeline

839 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