• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 449
  • Last Modified:

Having Count Query ?

I have a data set that i need to query to return only the rows were a week # is listed more than two times (attached snapshot of the data).  I am assuming I need to use a Having Count(Distinct) query, but i cannot get the logic correct.

From the data set JPEG, I only want the query to return the highlighted data because the week number is listed more than twice.  For example, the query should only return the rows with the weeks 41 and 45 listed.


Data-Set.jpg
0
szadroga
Asked:
szadroga
  • 6
  • 5
  • 3
1 Solution
 
Rey Obrero (Capricorn1)Commented:
select division,stafftype,[week],weekstart
from tablex
where tablex.week in(select [week] from tablex as X group by [week] having count(*)>2)

0
 
szadrogaAuthor Commented:
That query did not accomplish the proper data set.  Not sure if this makes a difference, but I am doing this in Access and i went to the SQL View for queries and pasted that code.  Replacing with the proper table name.
0
 
Rey Obrero (Capricorn1)Commented:
post the sql of the query that you are using
0
Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
SharathData EngineerCommented:
SELECT *
  FROM YourTable
 WHERE Week IN (SELECT Week FROM YourTable GROUP BY Week HAVING COUNT(Week) > 2)
0
 
szadrogaAuthor Commented:
Here is my current SQL statement that I am using.  

This statement gives me the result set I posted yesterday.
SELECT [SZ qUtilization by Staff Type].Division, [SZ qUtilization by Staff Type].[Staff Type], [SZ qUtilization by Staff Type].Week, [SZ qUtilization by Staff Type].[Week Start]
FROM [SZ qUtilization by Staff Type]
GROUP BY [SZ qUtilization by Staff Type].Division, [SZ qUtilization by Staff Type].[Staff Type], [SZ qUtilization by Staff Type].Week, [SZ qUtilization by Staff Type].[Week Start]
HAVING ((([SZ qUtilization by Staff Type].[Week Start]) Not Like ("*/2009")))
ORDER BY [SZ qUtilization by Staff Type].[Staff Type];

Open in new window

0
 
Rey Obrero (Capricorn1)Commented:
is the query you posted above a saved query?

use the name of that query to replace the name of the table..
0
 
szadrogaAuthor Commented:
Yes that is a saved query.  So I will use that query name as the table name in the query you posted?
0
 
szadrogaAuthor Commented:
Still no luck.  The name of the saved query is 'SZ qBad Data'.  Here is the query i tried:
select [division],[staff type],[week],[week start]
from [SZ qBad Data]
where [SZ qBad Data].week in(select [week] from [SZ qBad Data] as X group by [week] having count(*)>2)

Open in new window

0
 
Rey Obrero (Capricorn1)Commented:
post sample data from the TABLE..
0
 
szadrogaAuthor Commented:
Sample data attached
Bad-Data.jpg
0
 
Rey Obrero (Capricorn1)Commented:
would be better if i don't have to recreate the table.. what do you think?
0
 
SharathData EngineerCommented:
Just run this query and check whether it displays records having weeks 46,47 and 51
select [week] from [SZ qBad Data] group by [week] having count(*)>2

Open in new window

0
 
szadrogaAuthor Commented:
It is a much larger table than is displayed in that image.  That is just a snapshot of the data.
0
 
SharathData EngineerCommented:
what i am telling is if your inner query is returning some result then the outer query should give you the result. Its pretty simple query.
0
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.

Join & Write a Comment

Featured Post

Cloud Class® Course: C++ 11 Fundamentals

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

  • 6
  • 5
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now