asked on

sql query count

I have the following query

SELECT TOP (100) PERCENT dbo.case_id.case_id, dbo.address.a13_code AS Owner, dbo.case_id.received,
dbo.address.given_names + ' ' + dbo.address.last_name AS [Owner Name], dbo.issue.c05_code AS [Reason Code],
dbo.category_code.description1 AS [Reason description], dbo.issue.c31_code AS [Issue Description]
FROM dbo.case_id INNER JOIN
dbo.address ON dbo.case_id.company_id = dbo.address.company_id AND dbo.case_id.address_id = dbo.address.address_id INNER JOIN
dbo.issue ON dbo.case_id.company_id = dbo.issue.company_id AND dbo.case_id.case_id = dbo.issue.case_id INNER JOIN
dbo.category_code ON dbo.issue.company_id = dbo.category_code.company_id AND dbo.issue.c05_code = dbo.category_code.code
WHERE (dbo.case_id.company_id = 'mis') AND (dbo.case_id.b13_code IN ('ffls', 'ftls', 'rfls', 'rtls', 'cosfls', 'costls', 'napa', 'datahway', '1.20', 'fls', 'tls', 'sls',
'rowinstall'))
GROUP BY dbo.case_id.case_id, dbo.address.a13_code, dbo.case_id.received, dbo.address.given_names + ' ' + dbo.address.last_name, dbo.issue.c05_code,
dbo.category_code.description1, dbo.issue.c31_code
HAVING (dbo.case_id.received BETWEEN CONVERT(DATETIME, '2008-10-19 00:00:00', 102) AND CONVERT(DATETIME, '2008-10-25 23:59:59', 102)) AND
(dbo.address.given_names + ' ' + dbo.address.last_name <> ' ') AND (NOT (dbo.address.a13_code IS NULL))
ORDER BY [Owner Name], dbo.case_id.received

The user will change the bewteen values for dbo.case_id.received before running the query.

The user only wants to see rows for which the same owner name occurs more than once.

So in the example output in the attached file, the user would not want to see the last 3 rows since the owner name only occurs once for the date range.

I'm just not sure how to change the query for this to happen.

output-example.doc

andy12279

Could you add in your HAVING clause "AND Count([Owner Name]) > 1"

Daniel Wilson

Andy, I think you're right ... but would include [Owner Name] in the Group By clause too.

Sharath S

Put the result of your query in a temporary table.
SELECT ---
INTO #TempTable
FROM ---
WHERE ----
GROUP BY ---
HAVING ---
ORDER BY ---

Then run the below query.
Select * From #TempTable Where [Owner Name] = (Select [Owner Name] From #TempTable Group By [Owner Name] Having Count([Owner Name]) > 1)

yuching

try this

SELECT TOP (100) PERCENT dbo.case_id.case_id, dbo.address.a13_code AS Owner,
dbo.case_id.received, dbo.address.given_names + ' ' + dbo.address.last_name AS [Owner Name],
dbo.issue.c05_code AS [Reason Code], dbo.category_code.description1 AS [Reason description],
dbo.issue.c31_code AS [Issue Description]
FROM dbo.case_id
INNER Join dbo.address ON dbo.case_id.company_id = dbo.address.company_id
AND dbo.case_id.address_id = dbo.address.address_id
INNER Join dbo.issue ON dbo.case_id.company_id = dbo.issue.company_id
AND dbo.case_id.case_id = dbo.issue.case_id
INNER Join dbo.category_code ON dbo.issue.company_id = dbo.category_code.company_id
AND dbo.issue.c05_code = dbo.category_code.code
WHERE (dbo.case_id.company_id = 'mis') AND
(dbo.case_id.b13_code IN ('ffls', 'ftls', 'rfls', 'rtls', 'cosfls', 'costls',
'napa', 'datahway', '1.20', 'fls', 'tls', 'sls', 'rowinstall'))
And dbo.case_id.received BETWEEN CONVERT(DATETIME, '2008-10-19 00:00:00', 102)
AND CONVERT(DATETIME, '2008-10-25 23:59:59', 102))
AND (dbo.address.given_names + ' ' + dbo.address.last_name <> ' ')
AND (NOT (dbo.address.a13_code IS NULL))
GROUP BY dbo.case_id.case_id, dbo.address.a13_code, dbo.case_id.received,
dbo.address.given_names + ' ' + dbo.address.last_name,
dbo.issue.c05_code, dbo.category_code.description1, dbo.issue.c31_code
Having Count([Ownername]) > 1

Daniel Wilson

the temp table options is going to be slower. You don't want to use those unless you really MUST.

johnnyg123

ASKER

yuching

Thanks so much for your response

Unfortunately I get the following error

Invalid column name 'Ownername'.
The problem is the Having Count([Ownername]) > 1 statement

I noticed that the Ownername had a space so I tried Owner Name but still same error

I think the problem is that it doesn't like the fact that owner name is a concatenated field and for the count clause this is an issue

I tried Having Count(dbo.address.given_names + ' ' + dbo.address.last_name) > 1 but that returned no records and I know there are records that have the same name

any ideas?

andy12279

instead of this Count( [Owner Name] ) > 1
try
Count('Owner Name') > 1

johnnyg123

ASKER

I have attached a screen shot of the query results of fthe following query

SELECT TOP (100) PERCENT dbo.case_id.case_id, dbo.address.a13_code AS Owner,
dbo.case_id.received, dbo.address.given_names + ' ' + dbo.address.last_name AS [OwnerName],
dbo.issue.c05_code AS [Reason Code], dbo.category_code.description1 AS [Reason description],
dbo.issue.c31_code AS [Issue Description]
FROM dbo.case_id
INNER Join dbo.address ON dbo.case_id.company_id = dbo.address.company_id
AND dbo.case_id.address_id = dbo.address.address_id
INNER Join dbo.issue ON dbo.case_id.company_id = dbo.issue.company_id
AND dbo.case_id.case_id = dbo.issue.case_id
INNER Join dbo.category_code ON dbo.issue.company_id = dbo.category_code.company_id
AND dbo.issue.c05_code = dbo.category_code.code
WHERE (dbo.case_id.company_id = 'mis') AND
(dbo.case_id.b13_code IN ('ffls', 'ftls', 'rfls', 'rtls', 'cosfls', 'costls',
'napa', 'datahway', '1.20', 'fls', 'tls', 'sls', 'rowinstall'))
And dbo.case_id.received BETWEEN CONVERT(DATETIME, '2008-10-19 00:00:00', 102)
AND CONVERT(DATETIME, '2008-10-25 23:59:59', 102)
AND (dbo.address.given_names + ' ' + dbo.address.last_name <> ' ')
AND (NOT (dbo.address.a13_code IS NULL))
GROUP BY dbo.case_id.case_id, dbo.address.a13_code, dbo.case_id.received,
dbo.address.given_names + ' ' + dbo.address.last_name,
dbo.issue.c05_code, dbo.category_code.description1, dbo.issue.c31_code
order by ('ownername')

rows 1 and 2 have the same owner name as does rows 3,4,5

if I replace the order by statement with

Having Count('ownername') > 1

0 rows are returned....not sure why

query-results.doc

andy12279

The reason that your query returns 0 is based on your GROUP BY very row is distinct mainly due to the case_id and Description so the count is 1 not greater than 1.

johnnyg123

ASKER

hmmm...I see

I tried the following

SELECT TOP (100) PERCENT dbo.case_id.case_id, dbo.address.a13_code AS Owner,
dbo.case_id.received, dbo.address.given_names + ' ' + dbo.address.last_name AS [OwnerName],
dbo.issue.c05_code AS [Reason Code], dbo.category_code.description1 AS [Reason description],
dbo.issue.c31_code AS [Issue Description]
FROM dbo.case_id
INNER Join dbo.address ON dbo.case_id.company_id = dbo.address.company_id
AND dbo.case_id.address_id = dbo.address.address_id
INNER Join dbo.issue ON dbo.case_id.company_id = dbo.issue.company_id
AND dbo.case_id.case_id = dbo.issue.case_id
INNER Join dbo.category_code ON dbo.issue.company_id = dbo.category_code.company_id
AND dbo.issue.c05_code = dbo.category_code.code
WHERE (dbo.case_id.company_id = 'mis') AND
(dbo.case_id.b13_code IN ('ffls', 'ftls', 'rfls', 'rtls', 'cosfls', 'costls',
'napa', 'datahway', '1.20', 'fls', 'tls', 'sls', 'rowinstall'))
And dbo.case_id.received BETWEEN CONVERT(DATETIME, '2008-10-19 00:00:00', 102)
AND CONVERT(DATETIME, '2008-10-25 23:59:59', 102)
AND (dbo.address.given_names + ' ' + dbo.address.last_name <> ' ')
AND (NOT (dbo.address.a13_code IS NULL)) and count('ownername') > 1
order by ('ownername')

but I get the dreaded

Msg 147, Level 15, State 1, Line 1
An aggregate may not appear in the WHERE clause unless it is in a subquery contained in a HAVING clause or a select list, and the column being aggregated is an outer reference.

any ideas around this?

ASKER CERTIFIED SOLUTION

andy12279

membership

Create a free account to see this answer

Signing up is free and takes 30 seconds. No credit card required.

See answer

johnnyg123

ASKER

hmmmm

We get hundreds of phone calls in a given time period, however only a small percentage call repeatedlyin a given time frame

The original goal was to run a query to see which owners are calling more than once in a given time period.

Based on what I'm seeing in terms of responses, this isn't possible