CODEWITHSUNDEEP
mysql
Error 1004
Error 1004

Reputation: 8240

SQL Count Values From the Top 6

i trying to create an SQL query to count how many times [Over]='Y' in the Top 6 selection and i receive an error in the merge query.

Query to get Top 6:

 SELECT TOP 6 [Number]
  ,[Over]
  ,[Goal]
 FROM [Test_Hermes].[dbo].[Test]
 ORDER BY Number desc

Query to count:

SELECT COUNT([Over]) 
FROM [dbo].[Test] 
WHERE [OVER] = 'Y';

Merge both queries:

SELECT COUNT([Over]) 
FROM (SELECT TOP 6 [Number]
  ,[Over]
  ,[Goal]
FROM [Test_Hermes].[dbo].[Test]
ORDER BY Number desc) 
WHERE [OVER] = 'Y';

any help will be appreciate! Thanks

Upvotes: 0

Views: 48

Answers (2)

Dillon_Su
Dillon_Su

Reputation: 91

You can not use an order by in a sub query.

SELECT COUNT([t1].[Over]) 
FROM (SELECT TOP 6 [Number]
    ,[Over] 
    ,[Goal]
  FROM [Test_Hermes].[dbo].[Test]) as t1
WHERE [OVER] = 'Y' ;

If you need the order to determine the top 6 use ROW_NUMBER()

SELECT COUNT([t1].[Over]) 
FROM (SELECT ROW_NUMBER() OVER(PARTITION BY [Number] ORDER BY [Number] DESC) as 'Rank'
    ,[Number]
    ,[Over] 
    ,[Goal]
  FROM [Test_Hermes].[dbo].[Test]) as t1 

WHERE [t1].[OVER] = 'Y' AND [t1].[Rank] <= 6;

Upvotes: 0

Bernd Buffen
Bernd Buffen

Reputation: 15057

Show this sample pls.

 SELECT
   [Number]
  ,[Over]
  ,[Goal]
  , SUM( If(OVER= 'Y',1,0)) AS CNT
 FROM [Test_Hermes].[dbo].[Test]
 ORDER BY Number desc
LIMIT 1,6;

Upvotes: 1

Related Questions