SQL Syntax

I'm simply trying to add one column ( SUM(a.popu) AS Units) to the query after a.sctype. When I add it after SUM(a.popu) in the first select and SUM(c.popu) in the second select I get errors.

SELECT a.job_id,
       a.sctype,
       a.esthours,
       b.poest,
       (a.esthours * b.poest) AS total
  FROM (  SELECT j.job_id, s.sctype, SUM(c.popu) / (SUM(c.popu) / SUM(c.popu / c.pouest)) AS esthours
            FROM ccode c
                 INNER JOIN sccode s ON s.sccode_id = c.sccode_id
                 INNER JOIN job j ON c.job_id = j.job_id
           WHERE j.job_id = 7398
             AND s.sctype IN (1,
                              2,
                              3,
                              4,
                              5,
                              6,
                              7)
             AND c.popu > 0
             AND (j.deleted != 'Y' OR j.deleted IS NULL)
             AND (c.deleted != 'Y' OR c.deleted IS NULL)
             AND (s.deleted != 'Y' OR s.deleted IS NULL)
        GROUP BY j.job_id, s.sctype) a
       INNER JOIN (SELECT x.sctype, x.poest
                     FROM (  SELECT s.sctype, MAX(c.popu) popu, c.poest
                               FROM ccode c
                                    JOIN sccode s ON s.sccode_id = c.sccode_id
                                    JOIN job j ON j.job_id = c.job_id
                              WHERE j.job_id = 7398
                                AND s.sctype IN (1,
                                                 2,
                                                 3,
                                                 4,
                                                 5,
                                                 6,
                                                 7)
                                AND c.popu > 0
                           GROUP BY j.job_id, s.sctype) x) b
           ON a.sctype = b.sctype;


JOB_ID   SCTYPE    Units      EstHours    POEST         TOTAL
7398          1           80,245      3280.53          24.64           80832.26
7398          3            40258      1893.76          25.09           47514.44
hdcowboyazAsked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
hdcowboyazConnect With a Mentor Author Commented:
figured it out....

SELECT
  a.job_id 'JOB ID',
  a.sctype 'TYPE',
  a.units 'UNITS',  
  FORMAT(a.esthours, 2) 'EstHours',
  b.poest 'RATE',
  FORMAT((a.esthours * b.poest), 2) 'EstCost',
  FORMAT((a.esthours * b.poest) / a.units, 4) 'UnitCost'
FROM
(SELECT
  j.JOB_ID,
  s.SCTYPE,
  SUM(c.POPU) AS UNITS,
  SUM(c.POPU)/(SUM(c.POPU)/SUM(c.POPU/c.POUEST)) AS EstHours
FROM ccode c
INNER JOIN sccode s ON s.sccode_id = c.sccode_id
INNER JOIN job j ON c.job_id = j.job_id
WHERE j.job_id = 7398
AND s.sctype IN (1,2,3,4,5,6,7)
AND c.popu > 0
AND (j.deleted != 'Y' OR j.deleted IS NULL)
AND (c.deleted != 'Y' OR c.deleted IS NULL)
AND (s.deleted != 'Y' OR s.deleted IS NULL)
GROUP BY j.job_id, s.sctype) a
INNER JOIN (SELECT x.sctype, x.poest
FROM (SELECT s.sctype, MAX(c.popu) popu, c.poest
FROM ccode c
JOIN sccode s ON s.sccode_id = c.sccode_id
JOIN job j ON j.job_id = c.job_id
WHERE j.job_id = 7398
AND s.sctype IN (1,2,3,4,5,6,7)
AND c.popu > 0
GROUP BY j.job_id, s.sctype) X) b
ON a.sctype = b.sctype;
0
 
chaauCommented:
It is not really clear what you are trying to achieve. I can see a column popu in your 'b' subquery. Perhaps including b.popu to the main Select clause will do the trick. Try just b.popu (without SUM) and see what it shows.
0
 
hdcowboyazAuthor Commented:
From previous help
0
All Courses

From novice to tech pro — start learning today.