May 4, 2017 at 1:17 pm
Mornign folks,
I've a query that returns a count for individual orders.
I then did a union and at the bottom now have a sum of the count.SELECT
CAST(Jobs.jobid as varchar(12) ) as 'Job ID'
, count (JobReferral.id) as 'Referrals'
FROM ...
WHERE ...
GROUP BY ...
HAVING ...
UNION ALL
SELECT
'Total Job Referrals'
, COUNT(JobReferral.id)
FROM ...
WHERE ...
It returns the following perfectly.Job ID Referrals
1224356 1
62346 1
8232545 3
Total Job Referrals 5
I then have another query that returns the individual listings of each Job ID.
Job ID Company Address Date
1224356 Canning Inc. 123 Main 04/05/2017
62346 Canning Inc. 123 Main 04/02/2017
8232545 Canning Inc. 123 Main 04/11/2017
8232545 Canning Inc. 123 Main 04/14/2017
8232545 Canning Inc. 123 Main 04/15/2017
The number of results (5) are the same as the Total Job Referrals above.
How would I do a query that returns text indicating the sum of the results from this, matches the Total Job Referrals count above?
Match?
This Matches
or
Match?
No Match
May 4, 2017 at 1:36 pm
I am assuming these are 2 different queries returning these. So you want a 3rd query then I presume?
How about using a CASE statement and COUNT()?
I'm assuming your second set of results is a table, I'm going to name it table 2.
So, you could do:SELECT CASE
WHEN (SELECT
COUNT(*)
FROM table2) = (SELECT
COUNT(JobReferral.id)
FROM ...
WHERE ...)
THEN 'MATCH'
ELSE 'No Match'
END AS [MATCH?]
where the FROM ... and WHERE ... is the same as what you used in the first query. Would that work?
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
May 4, 2017 at 3:13 pm
Hah, that 's perfect!
Apparently I stared at it too long and lost sight simultaneously.
Thank you!
May 4, 2017 at 3:22 pm
Glad I could help 🙂
Sometimes all it takes is a fresh pair of eyes on the problem to get a solution. It's funny how many times I spend all day on a comples problem and then having a good night sleep and coming in all fresh makes the problem simple.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply