473,375 Members | 1,551 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,375 software developers and data experts.

Subquery with count problem

Hi

I have a table KDOCUMENT with the columns
[DocID],
[Title],
[CreatedByUser],
[CreateDate],
[LastModifiedByUserName],
[LastModificationDate],
[Comment]

On this table I want to find the test documents so that they can be deleted, hence I have created the query to find documents with the words "test" and "delete" in [Title] and [Comment] looking like this:
SELECT [DocID], [Title],[CreatedByUser], [CreateDate],[LastModifiedByUserName], [LastModificationDate], [Comment]
FROM [DMSDB].[dbo].[KDOCUMENT]
WHERE [Title] LIKE '%test%'
OR [Comment] LIKE '%test%'
OR [Title] LIKE '%delete%'
OR [Comment] LIKE '%delete%'
OR [OnlineVersionsSize] = 0.
This one is working fine returning 5070 rows with documents containing test and delete.

Problem is that we only want to list the documents belonging to users that have X documents or more (for instance 20), because we have to contact the people and ask them which documents actually are test documents. If someone only have 1 document we don't want to bother them, it is more important to clean up the "real heavy" user's documents.
So, I created a query

SELECT COUNT([CreatedByUser]) AS 'Number of docs', [CreatedByUser]
FROM [DMSDB].[dbo].[KDOCUMENT]
WHERE [Title] LIKE '%test%'
OR [Comment] LIKE '%test%'
OR [Title] LIKE '%delete%'
OR [Comment] LIKE '%delete%'
OR [OnlineVersionsSize] = 0
GROUP BY [CreatedByUser]
HAVING COUNT([CreatedByUser]) > 20
ORDER BY COUNT([CreatedByUser]) DESC

returning correctly the number of documents for each user and the user name. It also limits itself to listing only users with a count of 20 or more.

Now, I want to combine these two, listing all documents with DocID and everything where a user has 20 documents or more. But when I do this:

SELECT [DocID], [Title],[CreatedByUser], [CreateDate], [LastModifiedByUserName], [LastModificationDate], [Comment]
FROM [DMSDB].[dbo].[KDOCUMENT]
WHERE [Title] LIKE '%delete%'
OR [Comment] LIKE '%test%'
OR [Title] LIKE '%test%'
OR [Comment] LIKE '%delete%'
OR [OnlineVersionsSize] = 0
AND [CreatedByUser] IN (
SELECT [CreatedByUser]
FROM [DMSDB].[dbo].[KDOCUMENT]
GROUP BY [CreatedByUser]
HAVING COUNT([CreatedByUser]) > 20)

ORDER BY [CreatedByUser]

I lists all the documents as in the first query, returning 5070 rows while it should return about 4715 (I know this by adding up the numbers in the count query).

It has been a long time since I've been working with SQL, and I am struggling a bit here. Can someone help me? I get the same result using EXISTS, and when using = I of course get the error that it is returning more than one row.
Nov 20 '08 #1
3 2492
ck9663
2,878 Expert 2GB
Hi

I have a table KDOCUMENT with the columns
[DocID],
[Title],
[CreatedByUser],
[CreateDate],
[LastModifiedByUserName],
[LastModificationDate],
[Comment]

On this table I want to find the test documents so that they can be deleted, hence I have created the query to find documents with the words "test" and "delete" in [Title] and [Comment] looking like this:
SELECT [DocID], [Title],[CreatedByUser], [CreateDate],[LastModifiedByUserName], [LastModificationDate], [Comment]
FROM [DMSDB].[dbo].[KDOCUMENT]
WHERE [Title] LIKE '%test%'
OR [Comment] LIKE '%test%'
OR [Title] LIKE '%delete%'
OR [Comment] LIKE '%delete%'
OR [OnlineVersionsSize] = 0.
This one is working fine returning 5070 rows with documents containing test and delete.

Problem is that we only want to list the documents belonging to users that have X documents or more (for instance 20), because we have to contact the people and ask them which documents actually are test documents. If someone only have 1 document we don't want to bother them, it is more important to clean up the "real heavy" user's documents.
So, I created a query

SELECT COUNT([CreatedByUser]) AS 'Number of docs', [CreatedByUser]
FROM [DMSDB].[dbo].[KDOCUMENT]
WHERE [Title] LIKE '%test%'
OR [Comment] LIKE '%test%'
OR [Title] LIKE '%delete%'
OR [Comment] LIKE '%delete%'
OR [OnlineVersionsSize] = 0
GROUP BY [CreatedByUser]
HAVING COUNT([CreatedByUser]) > 20
ORDER BY COUNT([CreatedByUser]) DESC

returning correctly the number of documents for each user and the user name. It also limits itself to listing only users with a count of 20 or more.

Now, I want to combine these two, listing all documents with DocID and everything where a user has 20 documents or more. But when I do this:

SELECT [DocID], [Title],[CreatedByUser], [CreateDate], [LastModifiedByUserName], [LastModificationDate], [Comment]
FROM [DMSDB].[dbo].[KDOCUMENT]
WHERE [Title] LIKE '%delete%'
OR [Comment] LIKE '%test%'
OR [Title] LIKE '%test%'
OR [Comment] LIKE '%delete%'
OR [OnlineVersionsSize] = 0
AND [CreatedByUser] IN (
SELECT [CreatedByUser]
FROM [DMSDB].[dbo].[KDOCUMENT]
GROUP BY [CreatedByUser]
HAVING COUNT([CreatedByUser]) > 20)

ORDER BY [CreatedByUser]

I lists all the documents as in the first query, returning 5070 rows while it should return about 4715 (I know this by adding up the numbers in the count query).

It has been a long time since I've been working with SQL, and I am struggling a bit here. Can someone help me? I get the same result using EXISTS, and when using = I of course get the error that it is returning more than one row.
Use parenthesis to group your conditions
Expand|Select|Wrap|Line Numbers
  1.  
  2. WHERE ([Title] LIKE '%delete%'                                                
  3.     OR [Comment] LIKE '%test%'                                                
  4.     OR [Title] LIKE '%test%'                                                
  5.     OR [Comment] LIKE '%delete%'                                            
  6.     OR [OnlineVersionsSize] = 0)
  7.    AND [CreatedByUser] IN (
  8.                SELECT [CreatedByUser]
  9.                              FROM [DMSDB].[dbo].[KDOCUMENT]
  10.                             GROUP BY [CreatedByUser]
  11.                            HAVING COUNT([CreatedByUser]) > 20)
  12.  
  13.  
Conditions are evaluated left to right. If one of those ORs is true, your whole condition is true.

Similar to math formula:

2 + 3 * 5 = 25

2 + (3 * 5) = 17

Happy coding!

-- CK
Nov 21 '08 #2
Use parenthesis to group your conditions
Expand|Select|Wrap|Line Numbers
  1.  
  2. WHERE ([Title] LIKE '%delete%'                                                
  3.     OR [Comment] LIKE '%test%'                                                
  4.     OR [Title] LIKE '%test%'                                                
  5.     OR [Comment] LIKE '%delete%'                                            
  6.     OR [OnlineVersionsSize] = 0)
  7.    AND [CreatedByUser] IN (
  8.                SELECT [CreatedByUser]
  9.                              FROM [DMSDB].[dbo].[KDOCUMENT]
  10.                             GROUP BY [CreatedByUser]
  11.                            HAVING COUNT([CreatedByUser]) > 20)
  12.  
  13.  
Conditions are evaluated left to right. If one of those ORs is true, your whole condition is true.

Similar to math formula:

2 + 3 * 5 = 25

2 + (3 * 5) = 17

Happy coding!

-- CK
Wow, thank you!

It works like a charm, and the best part is that I was not completely off, it was just some paranthesis missing.
I now just discovered one thing you might be able to help me with:
This query: SELECT [CreatedByUser]
FROM [DMSDB].[dbo].[KDOCUMENT]
GROUP BY [CreatedByUser]
HAVING COUNT([CreatedByUser]) > 20)
counts ALL users in the entire table, but I only want it to count the ones with the criteria listed in the first query (Title and Comment equals "test" and/or "delete". When I try to put:

WHERE ([Title] LIKE '%delete%'
OR [Comment] LIKE '%test%'
OR [Title] LIKE '%test%'
OR [Comment] LIKE '%delete%'
OR [OnlineVersionsSize] = 0

into the second query like this:

SELECT [DocID], [Title],[CreatedByUser], [CreateDate], [LastModifiedByUserName], [LastModificationDate], [Comment], [OnlineVersionsSize]
FROM [DMSDB].[dbo].[KDOCUMENT]
WHERE [CreatedByUser] IN (
SELECT [CreatedByUser]
FROM [DMSDB].[dbo].[KDOCUMENT]
WHERE ([Title] LIKE '%delete%'
OR [Comment] LIKE '%test%'
OR [Title] LIKE '%test%'
OR [Comment] LIKE '%delete%'
OR [OnlineVersionsSize] = 0)
GROUP BY [CreatedByUser]
HAVING COUNT([CreatedByUser]) > 20)

it shows 0 results. Any ideas?
There might be another way to do this, so I am very open for suggestions :)
Nov 21 '08 #3
ck9663
2,878 Expert 2GB
Do you have NULL in CreatedByUser ?

try :

Expand|Select|Wrap|Line Numbers
  1.  
  2. SELECT DocID, Title,CreatedByUser, CreateDate, LastModifiedByUserName, 
  3.    LastModificationDate, Comment, OnlineVersionsSize
  4. FROM DMSDB.dbo.KDOCUMENT
  5. WHERE CreatedByUser IN 
  6.    (
  7.       SELECT CreatedByUser
  8.       FROM DMSDB.dbo.KDOCUMENT
  9.       WHERE (Title LIKE '%delete%' OR Comment LIKE '%test%' OR Title LIKE '%test%' OR Comment LIKE '%delete%' OR OnlineVersionsSize = 0)
  10.          and CreatedByUser IS NOT NULL
  11.       GROUP BY CreatedByUser
  12.       HAVING COUNT(*) > 20
  13.    )
  14. WHERE CreatedByUser IS NOT NULL
  15.  
  16.  

You should be careful with IN. Read more about it here

-- CK
Nov 21 '08 #4

Sign in to post your reply or Sign up for a free account.

Similar topics

5
by: Haisoo Shin | last post by:
Hello.. I am working on a PHP/PEAR web page that shows statistics of how many people read a certain article during given period of time. I have, say, two tables called 'books' and 'logs'. The...
3
by: Nachi | last post by:
I am getting 2 resultsets depending on conditon, In the second conditon i am getting the above error could anyone help me.......... CREATE proc sp_count_AllNewsPapers @CustomerId int as...
2
by: Greg Stark | last post by:
I find I often want to be able to do joins against views where the view are aggregates on a column that has an index. Ie, something like SELECT a.*, v.n FROM a JOIN (select a_id,count(*) as n...
0
by: Greg Stark | last post by:
Postgresql 7.4b2 (approximately, compiled out of CVS) When I have a subquery that has a complex subquery as one of the result columns, and then that result column is used multiple times in the...
4
by: Kenny G | last post by:
Below is a query that I currently have. I need to produce a subquery so that the top five of the CodeCount is returned. I appreciate your help. SELECT .PX_SURGEON, .PX_CODE, Count(.PX_CODE)...
2
by: Edwin Pauli | last post by:
Hi, Yesterday i had upgrade my PostgreSQL server from version 7.2.4 to 7.4.1. There are troubles with a subquery after the upgrade. Here is the query: SELECT team_naam, team_id, wpim, (
3
by: laurenq uantrell | last post by:
I'm trying to return an integer from the following table that returns the number of unique cities: tblEmployees Name City John Boston Frank New York Jim Omaha Betty ...
0
by: JT | last post by:
I'm using access 2000 and with assistance thought I had this problem licked. This query works: SELECT Q3.id, Q3.date, Max(Q3.Rank) AS Ninetieth FROM query3 AS Q3 WHERE...
1
by: Aleck | last post by:
Hie. I have a trigger that monitors changes to my table fields but I get an error saying subquery returned more than one value.Below is the code for my trigger, hope you will figure out whats...
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...
0
by: ryjfgjl | last post by:
In our work, we often need to import Excel data into databases (such as MySQL, SQL Server, Oracle) for data analysis and processing. Usually, we use database tools like Navicat or the Excel import...
0
by: taylorcarr | last post by:
A Canon printer is a smart device known for being advanced, efficient, and reliable. It is designed for home, office, and hybrid workspace use and can also be used for a variety of purposes. However,...
0
by: Charles Arthur | last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
0
by: ryjfgjl | last post by:
If we have dozens or hundreds of excel to import into the database, if we use the excel import function provided by database editors such as navicat, it will be extremely tedious and time-consuming...
0
by: ryjfgjl | last post by:
In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...
1
by: Sonnysonu | last post by:
This is the data of csv file 1 2 3 1 2 3 1 2 3 1 2 3 2 3 2 3 3 the lengths should be different i have to store the data by column-wise with in the specific length. suppose the i have to...
0
by: Hystou | last post by:
There are some requirements for setting up RAID: 1. The motherboard and BIOS support RAID configuration. 2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.