Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 207
  • 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

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

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