2

I have been writing a query that allows me to select and count rows for specific product id's and shipment types. Within this data, what I am now trying to achieve is count which rows have a specific field populated (second member name) and which have not. Then return this as a separate column in my query results.

Here's the query which I have written:

select count(job.JobID) as itemsCount, Lookup_Pack.PackDescription, Lookup_Pack.PackCode, Lookup_Pack.ID, job.shipping,

CASE 
WHEN Job.secondMemForename <> '' THEN count(job.JobID)
ELSE 0
END AS [Extra card count]
from job 
inner join Lookup_Pack on Lookup_Pack.ID = job.packTypeID
where Lookup_Pack.PackType = 'REN'
AND job.createDate >= '2015-06-01' and Job.createDate <= '2015-06-30'
GROUP BY Lookup_Pack.PackDescription, Lookup_Pack.PackCode, Lookup_Pack.ID, Job.shipping

If I run this query, I get an error returned as I am not grouping by Job.secondMemForename:

[FreeTDS][SQL Server]Column 'job.secondMemForename' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

although Job.secondMemForename does not form part of the query results. I have subsequently added this field to the GROUP BY statement, the problem with this is that the data returned for all rows where the CASE applies is un-grouped as the Job.secondMemForename is different for all of them.

Any idea how I can resolve this?

Thanks. Steeve.

pnuts
  • 58,317
  • 11
  • 87
  • 139
steeveroucaute
  • 69
  • 2
  • 3
  • 9
  • May be a [duplicate](http://stackoverflow.com/questions/4611897/group-by-aggregate-function-confusion-in-sql) – Tim S. Jul 01 '15 at 16:07

1 Answers1

3

Change Count() to Sum() and add it before CASE

SUM (CASE WHEN Job.secondMemForename <> '' THEN 1 END)  AS [Extra card count]
Kaf
  • 33,101
  • 7
  • 58
  • 78