[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

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

COUNT Query

I have a query below, which I am having difficulty with, maybe with the joins or count

SELECT     job.contractid, jobcat.shortcode,  COUNT(job.jobid) AS Expr1
FROM         vw_job_abd_nodcr INNER JOIN
                      job ON vw_job_abd_nodcr.jobid = job.jobid INNER JOIN
                      complete ON job.jobid = complete.jobid inner JOIN
                      jobcat ON job.jobcatid = jobcat.jobcatid
GROUP BY job.contractid, jobcat.shortcode
ORDER BY job.contractid, jobcat.shortcode

This results in

shortcode   contractid         Expr1
C                    1                   163
D                    1                  120
E                    1                   11
F                    1                     5    
I                     1                     6
A                    2                     1
B                    2                     21
C                   2                     285
D                   2                      6
E                   2                      52

I jobcat table contains shortcode from A-I, I would like to include all these shortcodes even if there is no value for it,
i.e

shortcode   contractid         Expr1
A                    1                    0
B                     1                   0
C                    1                   163
D                    1                  120
E                    1                   11
F                    1                     5
G                    1                     0
H                     1                    0      
I                     1                     6

I have tried left outer joins, but cannot get the correct results, can anybody help

Poly
0
polynominal
Asked:
polynominal
  • 8
  • 5
  • 2
  • +2
1 Solution
 
mikelittlewoodCommented:
Do you need to filter out the results where contractid = 2 as well?
0
 
mokuleCommented:
SELECT     job.contractid, jobcat.shortcode,  COUNT(job.jobid) AS Expr1
FROM         vw_job_abd_nodcr INNER JOIN
                      job ON vw_job_abd_nodcr.jobid = job.jobid INNER JOIN
                      complete ON job.jobid = complete.jobid LEFT OUTER JOIN
                      jobcat ON job.jobcatid = jobcat.jobcatid
GROUP BY job.contractid, jobcat.shortcode
ORDER BY job.contractid, jobcat.shortcode

0
 
Renante EnteraCommented:
Hi polynominal!

Try this :

SELECT     job.contractid, jobcat.shortcode,  COUNT(*) AS Expr1
FROM         vw_job_abd_nodcr LEFT JOIN
                      job ON vw_job_abd_nodcr.jobid = job.jobid LEFT JOIN
                      complete ON job.jobid = complete.jobid LEFT JOIN
                      jobcat ON job.jobcatid = jobcat.jobcatid
GROUP BY job.contractid, jobcat.shortcode
ORDER BY job.contractid, jobcat.shortcode

Hope this helps you.  Just try it.


Goodluck!
eNTRANCE2002 :-)
0
NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

 
mikelittlewoodCommented:
I think mine is the same as mokules ... but I HATE WITH A PASSION not using a where clause.
How you guys use the words INNER JOIN OUTER JOIN etc all the time I dont know.
Its about the most unreadable form of english I could think of.
Im sure most of you use it and are fine with it, so dont berate me about it  :o)

SELECT
  job.contractid, jobcat.shortcode, COUNT(job.jobid) AS Expr1
FROM
  vw_job_abd_nodcr, job, complete, jobcat
WHERE
  vw_job_abd_nodcr.jobid = job.jobid
AND
  job.jobid = complete.jobid
AND
  job.jobcatid =* jobcat.jobcatid
GROUP BY
  job.contractid, jobcat.shortcode
ORDER BY
  job.contractid, jobcat.shortcode
0
 
mokuleCommented:
And try this

SELECT     job.contractid, jobcat.shortcode,  COUNT(job.jobid) AS Expr1
FROM         vw_job_abd_nodcr INNER JOIN
                      job ON vw_job_abd_nodcr.jobid = job.jobid INNER JOIN
                      complete ON job.jobid = complete.jobid RIGHT OUTER JOIN
                      jobcat ON job.jobcatid = jobcat.jobcatid
GROUP BY job.contractid, jobcat.shortcode
ORDER BY job.contractid, jobcat.shortcode

0
 
mikelittlewoodCommented:
job.jobcatid =* jobcat.jobcatid    is the same as    RIGHT OUTER JOIN jobcat ON job.jobcatid = jobcat.jobcatid  

am I right?
0
 
polynominalAuthor Commented:
Thanks for the answers,, unfortunately none of them are bringing 0 values through
0
 
mikelittlewoodCommented:
Why dont you start small and work your way up to the final set of data.
Just bring back data from job and jobcat first as this is where you are counting.
Then put in the other tables one by one until you find out what the issue is.

SELECT
  job.contractid, jobcat.shortcode, COUNT(job.jobid) AS Expr1
FROM
  job, jobcat
WHERE
  job.jobcatid =* jobcat.jobcatid
GROUP BY
  job.contractid, jobcat.shortcode
ORDER BY
  job.contractid, jobcat.shortcode
0
 
polynominalAuthor Commented:
Hi, I have tried everything, including just joining 2 tables and I still cant get 0 values.
0
 
polynominalAuthor Commented:
I have stripped it down to

SELECT     job.contractid, jobcat.shortcode, COUNT(job.jobid) AS Expr1
FROM         job LEFT OUTER JOIN
                      jobcat ON jobcat.jobcatid = job.jobcatid
GROUP BY job.contractid, jobcat.shortcode
ORDER BY job.contractid, jobcat.shortcode

With right or left outer joins to no avail, is it to do with the Count function

0
 
mikelittlewoodCommented:
Actually I think the problem is that no one has brought back all the shortcodes from the jobcat table.
You might have to do a nested select to get the rest of the data instead though I could be wrong.
Gimme a sec to test something.
0
 
mikelittlewoodCommented:
shortcode   contractid         Expr1
A                    1                    0
B                     1                   0
C                    1                   163
D                    1                  120
E                    1                   11
F                    1                     5
G                    1                     0
H                     1                    0      
I                     1                     6

ok the issue is that the shortcodes I presume can be linked to 0, 1 or more contractid's.
so if shortcode A can be linked to contractid 0, 1, 2 for example, how do you decide what contractid to bring back for the shortcode, if any at all. If you dont know what contractid to bring back, then you cant even count the jobid's linked to the contractid's.
Or that is how I am reading your tables.
Does that make sense?
0
 
polynominalAuthor Commented:
If I put a clause in saying where contractid in (1,2) for instance, this limits what I want to bring back, but it still doesnt return 0 for contractid that has no shortcode A for instance
0
 
mikelittlewoodCommented:
Thats the problem though.
Even if you limit down the contractid's to 1 and 2, you still dont know what contractid you want to display next to the shortcode.
Unless you want to see results below like

shortcode   contractid         Expr1
A                    1                   0
A                    2                   1
B                    1                   0
B                    2                   12
C                    1                   163
D                    1                   120
E                    1                   11
E                    2                   5
F                    1                   5
G                    1                   0


H                    1                   0
H                    2                   0
I                    1                    6
0
 
polynominalAuthor Commented:
So basically this cannot be done. just starting SQL so please ignore my ignorance. Im presenting this to my boss tomorrow, so I could be in a bit of stuck
0
 
auke_tCommented:
-- Try this:

SELECT
    job.contractid, jobcat.shortcode, COUNT(job.jobid) AS Expr1
FROM        
    jobcat
LEFT OUTER JOIN
   job
ON
   jobcat.jobcatid = job.jobcatid
GROUP BY
   job.contractid,
   jobcat.shortcode
ORDER BY
   job.contractid,
   jobcat.shortcode
0
 
auke_tCommented:
--This one is probably better:

SELECT
      contact.contractid,
      jobcat.shortcode,
      COUNT(job.jobid) AS jobs
FROM        
      jobcat
LEFT OUTER JOIN
(
      SELECT
            distinct
            contactid
      FROM
            job
) contact
ON
      1 = 1
LEFT OUTER JOIN
      job
ON
      jobcat.jobcatid = job.jobcatid
AND
      contact.contactid = job.contactid
GROUP BY
      contact.contractid,
      jobcat.shortcode
ORDER BY
      contact.contractid,
      jobcat.shortcode
0
 
mikelittlewoodCommented:
The main point is that your shortcode is more of an informatioin field rather than a link between the tables so I would say no.
You could write something that is not contractid specific.

SELECT
  jobcat.shortcode,
  (SELECT COUNT(job.jobid) FROM job WHERE job.jobcatid = jobcat.jobcatid) AS Expr1
FROM
  jobcat
GROUP BY
  jobcat.shortcode
ORDER BY
  jobcat.shortcode

I think that would return you a list of all shortcodes and how many jobid's contain the specific code.
Is that any help?

I presume your jobcat table only holds a single record per category letter.
i.e.
Shortcode/JobcatID
A  1
B  2
C  3
D  4
E  5
F  6
G  7
H  8
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!

  • 8
  • 5
  • 2
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now