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

# 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
1 Solution

Commented:
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

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