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
johnnyg123Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

andy12279Commented:
Could you add in your HAVING clause "AND Count([Owner Name]) > 1"
0
Daniel WilsonCommented:
Andy, I think you're right ... but would include [Owner Name] in the Group By clause too.
0
SharathData EngineerCommented:
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)
0
Cloud Class® Course: C++ 11 Fundamentals

This course will introduce you to C++ 11 and teach you about syntax fundamentals.

yuchingCommented:
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
0
Daniel WilsonCommented:
the temp table options is going to be slower.  You don't want to use those unless you really MUST.
0
johnnyg123Author Commented:
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?
0
andy12279Commented:
instead of this Count( [Owner Name] ) > 1
try
Count('Owner Name') > 1
0
johnnyg123Author Commented:
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
0
andy12279Commented:
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.

 
0
johnnyg123Author Commented:
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?
0
andy12279Commented:
the Select and Group By have to have the same columns.  If your user needs the case_id and the reason description in your select statement, then you will have to add them back into the group by statement and take out the Having statement to get results.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
johnnyg123Author Commented:
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
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2005

From novice to tech pro — start learning today.

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.