CODEWITHSUNDEEP
sql-server
user3062459
user3062459

Reputation: 1631

SQL Server GROUP BY and Filter Top 10%

I am trying to build a query that first GROUP BY quarter and then gets top 10% of the sales value. Example: If I GROUP BY quarter the results for 2007 Q1 should be 4,175 rows, which means that after it goes through the 10% calc (at bottom of script) I should get 417 rows for 2007 Q1. The query I had before this was Grouping by correctly i.e. 4,174 for 2007 Q1, but after the top 10% filter I got an incorrect value of 263 rows.

I edited that query to arrive at the query below. Now I am getting the following error: Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

What is the correct way of writing this query?

SELECT
      [quarter]
      ,[address]
      ,[aptmnt]
      ,[listed]
      ,[cs_date]
      ,[updted]
      ,[price]
      ,[totsqft]
      ,[rooms]
      ,[bdrms]
      ,[baths]
      ,[sect]
FROM [dbo].[top10salesbyyear] p1

 WHERE 

(SELECT COUNT(*)  FROM [dbo].[top10salesbyyear] p2 GROUP BY
     [quarter]
     ,[address]
     ,[aptmnt]
     ,[listed]
     ,[cs_date]
     ,[updted]
     ,[price]
     ,[totsqft]
     ,[rooms]
     ,[bdrms]
     ,[baths]
     ,[sect]

 HAVING p2.price >= p1.price)<=
   (SELECT 0.1 * COUNT(*) FROM [dbo].[top10salesbyyear])

Upvotes: 0

Views: 370

Answers (2)

Juan Carlos Oropeza
Juan Carlos Oropeza

Reputation: 48197

Is simple if you use Windows Functions

WITH CTE as (
      SELECT *,
             count(*) OVER (partition by quarter) as total_rows,
             row_number() OVER (partition by quarter order by price DESC) as rn 
      FROM [dbo].[top10salesbyyear]
)
SELECT *
FROM CTE
WHERE rn < total_row * 0.1

Upvotes: 1

Bacon Bits
Bacon Bits

Reputation: 32170

I would just use the NTILE() window function:

;WITH CTE as (
    SELECT *,
        NTILE(10) OVER (PARTITION BY quarter ORDER BY Price DESC) as NTILE
    FROM [dbo].[top10salesbyyear]
)
SELECT *
FROM CTE
WHERE NTILE = 1

Upvotes: 2

Related Questions