0

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?

J. Doe
  • 71
  • 1
  • 6
  • 2
    apparently that `DBD::CSV` thing does not really support anything than the most trivial SQL statements - it appears to not even support derived tables. If you want to learn SQL you should use a real DBMS, e.g. Postgres not some fake SQL interface to a CSV file –  Apr 19 '18 at 19:48

1 Answers1

3

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
J. Doe
  • 71
  • 1
  • 6
Aura
  • 1,283
  • 2
  • 16
  • 30
  • My DB dont support 'with'. `Command 'WITH' not recognized or not supported! ` Im use this lib: http://search.cpan.org/~rehsack/SQL-Statement-1.412/lib/SQL/Statement/Syntax.pod – J. Doe Apr 19 '18 at 19:19
  • Then you can create a temp table and store values of inner select query in temp table and then select it from main query. – Aura Apr 19 '18 at 19:21
  • With Your second solution I have error:`Bad table or column name: 'SELECT' is a SQL reserved word! at /usr/local/lib/perl5/site_perl/5.26.1/SQL/Statement.pm line 90. Bad table or column name: 't.' has chars not alphanumeric or underscore! at /usr/local/lib/perl5/site_perl/5.26.1/SQL/Statement.pm line 90.` – J. Doe Apr 19 '18 at 19:40