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

x
?
Solved

sql query count

Posted on 2008-11-03
12
Medium Priority
?
198 Views
Last Modified: 2011-10-19
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
0
Comment
Question by:johnnyg123
  • 4
  • 4
  • 2
  • +2
12 Comments
 
LVL 2

Expert Comment

by:andy12279
ID: 22871642
Could you add in your HAVING clause "AND Count([Owner Name]) > 1"
0
 
LVL 32

Expert Comment

by:Daniel Wilson
ID: 22871722
Andy, I think you're right ... but would include [Owner Name] in the Group By clause too.
0
 
LVL 41

Expert Comment

by:Sharath
ID: 22871802
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
Fill in the form and get your FREE NFR key NOW!

Veeam is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

 
LVL 11

Expert Comment

by:yuching
ID: 22873026
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
 
LVL 32

Expert Comment

by:Daniel Wilson
ID: 22875814
the temp table options is going to be slower.  You don't want to use those unless you really MUST.
0
 

Author Comment

by:johnnyg123
ID: 22895556
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
 
LVL 2

Expert Comment

by:andy12279
ID: 22895736
instead of this Count( [Owner Name] ) > 1
try
Count('Owner Name') > 1
0
 

Author Comment

by:johnnyg123
ID: 22904362
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
 
LVL 2

Expert Comment

by:andy12279
ID: 22904826
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
 

Author Comment

by:johnnyg123
ID: 22905003
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
 
LVL 2

Accepted Solution

by:
andy12279 earned 2000 total points
ID: 22905282
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
 

Author Comment

by:johnnyg123
ID: 22905718
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

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
This video shows how to quickly and easily deploy an email signature for all users in Office 365 and prevent it from being added to replies and forwards. (the resulting signature is applied on the server level in Exchange Online) The email signat…
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…

580 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question