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

Grouping Problem

Hi I have developed the following query below

SELECT     contract.contractid, COUNT(dfe.authorised) AS dfecount, CASE WHEN SUM(templateprice.price * workitem.quantity)
                      > 0 THEN (templateprice.price * workitem.quantity) ELSE 0 END AS positive, CASE WHEN SUM(templateprice.price * workitem.quantity)
                      <= 0 THEN (templateprice.price * workitem.quantity) ELSE 0 END AS negative
FROM         workitem INNER JOIN
                      templateprice INNER JOIN
                      template ON templateprice.templateid = template.templateid ON workitem.templateid = template.templateid INNER JOIN
                      dfe ON workitem.dfeid = dfe.dfeid INNER JOIN
                      job INNER JOIN
                      contract ON job.contractid = contract.contractid INNER JOIN
                      jobcat ON job.jobcatid = jobcat.jobcatid ON dfe.jobid = job.jobid INNER JOIN
                      complete ON job.jobid = complete.jobid
WHERE     (contract.contractid IN (2, 16)) AND (RIGHT(job.estimate, 4) <> '0000') AND (jobcat.shortcode = 'G') AND (job.newsitesid IS NOT NULL) AND
                      (job.newsitesid <> '')
GROUP BY contract.contractid

I keep getting an error templateprice.price is not ocntained in the aggregate summary or group by clause, when I add it in and workitem.quantity the groupings are repaeated many times

Can anybody help

Thanks

P
0
polynominal
Asked:
polynominal
1 Solution
 
ispalenyCommented:
SELECT     contract.contractid, COUNT(dfe.authorised) AS dfecount, CASE WHEN SUM(templateprice.price * workitem.quantity)
                      > 0 THEN SUM(templateprice.price * workitem.quantity) ELSE 0 END AS positive, CASE WHEN SUM(templateprice.price * workitem.quantity)
                      <= 0 THEN SUM(templateprice.price * workitem.quantity) ELSE 0 END AS negative
FROM         workitem INNER JOIN
                      templateprice INNER JOIN
                      template ON templateprice.templateid = template.templateid ON workitem.templateid = template.templateid INNER JOIN
                      dfe ON workitem.dfeid = dfe.dfeid INNER JOIN
                      job INNER JOIN
                      contract ON job.contractid = contract.contractid INNER JOIN
                      jobcat ON job.jobcatid = jobcat.jobcatid ON dfe.jobid = job.jobid INNER JOIN
                      complete ON job.jobid = complete.jobid
WHERE     (contract.contractid IN (2, 16)) AND (RIGHT(job.estimate, 4) <> '0000') AND (jobcat.shortcode = 'G') AND (job.newsitesid IS NOT NULL) AND
                      (job.newsitesid <> '')
GROUP BY contract.contractid
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

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