[SOLVED] SQL - COUNT results from SELECT

SQL - COUNT results from SELECT

I would like to count all the repetitions of the maximum values from the profit column for all asset (they repeat, and I want to choose the best result from each unique repetition), and finally group the results according to the value of the strategy column.

I use DBD::CSV for Perl, its using SQL statements like here: http://search.cpan.org/~rehsack/SQL-Statement-1.412/lib/SQL/Statement/Syntax.pod

But that is not important. What I care about most is to improve my syntax, and I should deal with the rest of the matter.

SELECT COUNT(*) AS dupa 
FROM (SELECT asset, strategy, MAX(profit) 
      FROM a1.csv 
      GROUP BY strategy);

Output: Bad table or column name: 'SELECT' is a SQL reserved word!

Another example:

SELECT * AS xxx FROM (SELECT strategy, profit FROM a1.csv GROUP BY strategy)

Output: Bad table or column name: 'SELECT' is a SQL reserved word!

It's only example. I tried different combinations and there are always errors. I wanted to ask what is wrong in my syntax?

Solution

You can store your query from 'FROM' clause in CTE like following:

with cte as(
SELECT asset, strategy, MAX(profit) FROM a1.csv GROUP BY strategy
)
Select count(*)
From cte

Here is another approach, can you please try following code:

SELECT COUNT(t.*) AS dupa 
FROM (SELECT asset, strategy, MAX(profit) 
      FROM a1.csv 
      GROUP BY strategy) t

Okay, this is another approach to it using temporary table like I stated before:

CREATE TEMP TABLE TempTable AS 
SELECT asset, strategy, MAX(profit) FROM a1.csv GROUP BY asset, strategy

SELECT COUNT(*) FROM TempTable