CODEWITHSUNDEEP
sqlsqlite
user1384636
user1384636

Reputation: 501

Sqlite and nested query in the from statement

I am currently working with SQLite and I am freaking out with this query 

    SELECT Cur.Name, SUM(Cur.Added+Cur.Removed) AS Lines, COUNT(DISTINCT(Cur.Author)) AS authCurrent,
       ( SELECT COUNT(DISTINCT(Author)) 
         FROM Change
         WHERE Extencion = "java" AND
             Date >= date('2012-04-08') AND
             Date < date('2012-04-15')
         ) AS totCurrent
    FROM 
       Change AS Cur,
       ( SELECT a.Name, COUNT(DISTICT(a.Author)) AS authLast
         FROM Change AS a
         WHERE a.Extencion = "java" AND
           a.Date >= date('2012-04-08') AND
           a.Date < date('2012-04-15')
       GROUP BY a.Name ) AS Previous
    WHERE
       Previous.Name = Cur.Name
       Cur.Extencion = "java" AND
       Cur.Date >= date('2012-04-15') AND
       Cur.Date < date('2012-04-22')
   GROUP BY Cur.Name

and it says "Query Error: near "Cur": syntax error Unable to execute statement" I don't know, can it be that the query is not executable because SQLite does not support Nested Queries?

Anyway, the Table Change has the following form

      Author | Path | Extencion | Name | Date | Added | Removed

Upvotes: 1

Views: 2267

Answers (1)

Taryn
Taryn

Reputation: 247710

It appears that you spelled DISTINCT wrong in this line:

COUNT(DISTICT(a.Author)) AS authLast  -- change to DISTINCT

Also have you tried your query this way:

SELECT Cur.Name, 
  SUM(Cur.Added+Cur.Removed) AS Lines, 
  COUNT(DISTINCT(Cur.Author)) AS authCurrent,
  Previous.authLast AS totCurrent
FROM Change AS Cur
LEFT JOIN 
( 
  SELECT a.Name, COUNT(DISTINCT(a.Author)) AS authLast
  FROM Change AS a
  WHERE a.Extencion = "java" AND
    a.Date >= date('2012-04-08') AND
    a.Date < date('2012-04-15')
  GROUP BY a.Name
) AS Previous
  ON Previous.Name = Cur.Name
WHERE 
       Cur.Extencion = "java" AND
       Cur.Date >= date('2012-04-15') AND
       Cur.Date < date('2012-04-22')
GROUP BY Cur.Name, Previous.authLast

Upvotes: 2

Related Questions