April 30, 2003 at 2:26 pm
Hello,
I've written a query which tells me the number of times a postcode appears in a table. It looks like this:
SELECT
t_Retail.Pcde6P,
Count(*) AS Total
FROM t_Retail
WHERE
LEN (TRIM(t_Retail.Pcde6P)) = 6
GROUP BY
t_Retail.Pcde6P
HAVING
Count(t_Retail.Pcde6P) > 2
ORDER BY
Count(*) DESC
My problem is that I am trying to use the above query as a sub-query, so that in my outer query, I can have the following in my where clause: WHERE t_Retail.Pcde6P IN
SELECT
t_Retail.Pcde6P,
Count(*) AS Total
FROM t_Retail
WHERE
LEN (TRIM(t_Retail.Pcde6P)) = 6
GROUP BY
t_Retail.Pcde6P
HAVING
Count(t_Retail.Pcde6P) > 2
ORDER BY
Count(*) DESC
I want to avoid selecting Count(*) in my sub-query, but still return the postcodes that meet the criteria.
Can anyone suggest to me how I can achieve this?
Kind Regards, Sam
April 30, 2003 at 2:50 pm
quote:
Hello,I've written a query which tells me the number of times a postcode appears in a table. It looks like this:
SELECT
t_Retail.Pcde6P,
Count(*) AS Total
FROM t_Retail
WHERE
LEN (TRIM(t_Retail.Pcde6P)) = 6
GROUP BY
t_Retail.Pcde6P
HAVING
Count(t_Retail.Pcde6P) > 2
ORDER BY
Count(*) DESC
My problem is that I am trying to use the above query as a sub-query, so that in my outer query, I can have the following in my where clause: WHERE t_Retail.Pcde6P IN
SELECT
t_Retail.Pcde6P,
Count(*) AS Total
FROM t_Retail
WHERE
LEN (TRIM(t_Retail.Pcde6P)) = 6
GROUP BY
t_Retail.Pcde6P
HAVING
Count(t_Retail.Pcde6P) > 2
ORDER BY
Count(*) DESC
I want to avoid selecting Count(*) in my sub-query, but still return the postcodes that meet the criteria.
Can anyone suggest to me how I can achieve this?
Kind Regards, Sam
Below is the script for creating the table
CREATE TABLE [dbo].[t_Retail] (
[Pcde6P] [nvarchar] (6) ,
[Housenumber] [nvarchar] (25),
[Total transaction] [real] NULL ,
[Number of products] [int] NULL ,
[Store number] [nvarchar] (3) ,
[Day] [nvarchar] (2) ,
[Month] [nvarchar] (2)
) ON [PRIMARY]
GO
May 1, 2003 at 6:56 am
One way is to include TOP clause for subquery
SELECT *
from t_Retail
WHERE t_Retail.Pcde6P IN
(
SELECT TOP 100 PERCENT
t_Retail.Pcde6P,
Count(*) AS Total
FROM t_Retail
WHERE
LEN (TRIM(t_Retail.Pcde6P)) = 6
GROUP BY
t_Retail.Pcde6P
HAVING
Count(t_Retail.Pcde6P) > 2
ORDER BY
Count(*) DESC
)
May 1, 2003 at 6:59 am
Sorry, also when you include TOP clause, you can remove count(*) from the select list of sub query.
SELECT *
FROM t_retail
where t_Retail.Pcde6P IN
(SELECT top 100 PERCENT
t_Retail.Pcde6P
FROM t_Retail
WHERE
LEN (LTRIM(t_Retail.Pcde6P)) = 6
GROUP BY
t_Retail.Pcde6P
HAVING
Count(t_Retail.Pcde6P) > 2
ORDER BY
Count(*) DESC
)
May 1, 2003 at 7:36 am
Sam, see if this works
select t_Retail.Pcde6P from t_Retail
where exists
(SELECT
t_Retail.Pcde6P,
Count(*) AS Total
FROM t_Retail
WHERE
LEN (TRIM(t_Retail.Pcde6P)) = 6
GROUP BY
t_Retail.Pcde6P
HAVING
Count(t_Retail.Pcde6P) > 2)
Is this what you are looking for. This will give you a list of all the postcode that appear more than twice in your table
May 1, 2003 at 8:01 am
SELECT
t_Retail.Pcde6P,
Count(*) AS Total
into #temp
FROM t_Retail
WHERE
LEN (TRIM(t_Retail.Pcde6P)) = 6
GROUP BY
t_Retail.Pcde6P
HAVING
Count(t_Retail.Pcde6P) > 2
ORDER BY
Count(*) DESC
SELECT *
from t_Retail R
WHERE exists
( select *
from #temp TR
where R.Pcde6P = TR.Pcde6P)
drop table #temp
🙂
May 2, 2003 at 8:02 am
Why not user a join, it is faster and you do not need an top/order by in the subquery.
SELECT a.* FROM t_Retail a
FROM t_Retail a
INNER JOIN (SELECT Pcde6P
FROM t_Retail
WHERE LEN(RTRIM(Pcde6P)) = 6
GROUP BY Pcde6P
HAVING Count(Pcde6P) > 2) b ON b.Pcde6P = a.Pcde6P
Far away is close at hand in the images of elsewhere.
Anon.
May 6, 2003 at 7:30 am
Hi,
Simply removing Count(*) from SELECT clause of sub query and removing whole of ORDER BY clause because it is of no use when it's part of sub query in front of IN clause.
Following query should work:
SELECT o.*
FROM t_Retail o
WHERE o.Pcde6P IN (
SELECT i.Pcde6P
FROM t_Retail i
WHERE LEN(LTRIM(RTRIM(i.Pcde6P))) = 6
GROUP BY i.Pcde6P
HAVING Count(i.Pcde6P) > 2
)
For quick test, I've tried following query on 'Pubs' database. Query returns publishers who have published more than 2 titles.
select p.*
from publishers p
where p.pub_id in (
select t.pub_id
from titles t
group by t.pub_id
having count(*) > 2
)
Hope this helps.
Regards,
IP
May 8, 2003 at 10:02 am
You would be much better off using an exists clause if you had a pk on the table.
i.e.
SELECT *
FROM t_retail a
WHERE EXISTS ( SELECT b.Pcde6P
FROM t_retail b
WHERE b.pk = a.pk
AND len(trim(Pcde6P)) = 6
GROUP BY b.Pcde6P
HAVING COUNT(*) > 2)
Exists are much faster than IN's
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply