sql count query over a count query

Question

following is my query to get the count of documentid

select count(did),did 
from ( 
      select count(FileDataId) as fid, 
             documentid as did,
             FileDataId 
      from DocumentHistory 
      where documentid in (
                           select documentid 
                           from Document 
                           where DocumentTypeId=11 and 
                                 IsVerified=0 and 
                                 lastmodified < '5/jan/2019' 
                          ) 
      group by DocumentId,
               filedataid 
      having count(FileDataId)<2
      )

I am getting error as

Incorrect syntax near ')'.

If i run the inner query, it is bringing me result

I like to know how many times the did is repeating in the result

Whitespace makes your query infinitely easier to read. And, once you use it, you'll notice you didn't give your subquery in your FROM an alias; which is compulsory. Try SELECT * FROM (SELECT 1 as i); and you get the same error. — Thom A
– Thom A ♦, Commented Jan 16, 2019 at 22:19
Just add an alias to the subquery, and you need a group by too — Ilyes
– Ilyes, Commented Jan 16, 2019 at 22:19
Now that this is formatted into a legible format it is clear what the problem is. You have subquery as the source of your query but it has no alias. But honestly it seems something is not right here. did will have a count of exactly 1 for every single row because you have grouped them in the subquery. — Sean Lange
– Sean Lange, Commented Jan 16, 2019 at 22:20
Also, this is bad practice for ANSI standards lastmodified < '5/jan/2019' — S3S
– S3S, Commented Jan 16, 2019 at 22:21

Caius Jard · Accepted Answer · 2019-01-16 22:38:39Z

4

I guess it's because you haven't put an alias for your subquery. You're also missing a grouping in the outer query. The formatting of your query could be improved too:

select 
  count(a.did),
  a.did  
from ( 
  select 
    count(dh.FileDataId) as fid, 
    dh.documentid as did,
    dh.FileDataId 
  from DocumentHistory dh 
  INNER JOIN Document d on d.documentid = dh.documentid
  where d.DocumentTypeId=11 and d.IsVerified=0 and d.lastmodified < '2019-01-05'  
  group by DocumentId, filedataid 
  having count(FileDataId)<2
) a
GROUP BY did

As well as incorporating simon's suggestion surrounding the date format (yyyy-mm-dd is ISO, and not subject to localisation problems like a date that contains words- your query might not work on eg a Spanish database), I swapped the IN for an INNER JOIN too; though query optimizers can generally rewrite IN to behave like a join you should aim to avoid using IN for lists longer than you'd be prepared to write by hand. Some databases are better optimised for join than in

Note, having said that, that there's a slight difference in behaviour between an INNER JOIN and IN, if the IN(...) list contains duplicates you won't get repeated rows out of documenthistory result but you would with an inner join. (I expect that documentid is a primary key of document so duplicates wouldn't appear in this case)

edited Jan 16, 2019 at 22:38

answered Jan 16, 2019 at 22:18

Caius Jard

75k6 gold badges61 silver badges95 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Ilyes Over a year ago

GROUP BY did , since there is select count(did),did

makdu Over a year ago

This solution worked., Never thought that missing alias is the problem, Thank you

Caius Jard Over a year ago

Always alias all your subqueries, and actually it's always best to fully qualify all column names too

Collectives™ on Stack Overflow

sql count query over a count query

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related