CODEWITHSUNDEEP
sqlt-sql
Thimo Franken
Thimo Franken

Reputation: 330

COUNT is outputting more than one row

I am having a problem with my SQL query using the count function.

When I don't have an inner join, it counts 55 rows. When I add the inner join into my query, it adds a lot to it. It suddenly became 102 rows.

Here is my SQL Query:

SELECT COUNT([fmsStage].[dbo].[File].[FILENUMBER])
FROM [fmsStage].[dbo].[File]
INNER JOIN [fmsStage].[dbo].[Container]
    ON [fmsStage].[dbo].[File].[FILENUMBER] = [fmsStage].[dbo].[Container].[FILENUMBER]
WHERE [fmsStage].[dbo].[File].[RELATIONCODE] = 'SHIP02'
  AND [fmsStage].[dbo].[Container].DELIVERYDATE BETWEEN '2016-10-06' AND '2016-10-08'
GROUP BY [fmsStage].[dbo].[File].[FILENUMBER]

Also, I have to do TOP 1 at the SELECT statement because it returns 51 rows with random numbers inside of them. (They are probably not random, but I can't figure out what they are.)

What do I have to do to make it just count the rows from [fmsStage].[dbo].[file].[FILENUMBER]?

Upvotes: 3

Views: 97

Answers (2)

Madhivanan
Madhivanan

Reputation: 13700

Just remove GROUP BY Clause

SELECT COUNT([fmsStage].[dbo].[File].[FILENUMBER])
FROM [fmsStage].[dbo].[File]
INNER JOIN [fmsStage].[dbo].[Container]
    ON [fmsStage].[dbo].[File].[FILENUMBER] = [fmsStage].[dbo].[Container].[FILENUMBER]
WHERE [fmsStage].[dbo].[File].[RELATIONCODE] = 'SHIP02'
  AND [fmsStage].[dbo].[Container].DELIVERYDATE BETWEEN '2016-10-06' AND '2016-10-08'

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269973

First, your query would be much clearer like this:

SELECT COUNT(f.[FILENUMBER])
FROM [fmsStage].[dbo].[File] f INNER JOIN
     [fmsStage].[dbo].[Container] c
    ON v.[FILENUMBER] = c.[FILENUMBER]
WHERE f.[RELATIONCODE] = 'SHIP02' AND
      c.DELIVERYDATE BETWEEN '2016-10-06' AND '2016-10-08';

No GROUP BY is necessary. Otherwise you'll just one row per file number, which doesn't seem as useful as the overall count.

Note: You might want COUNT(DISTINCT f.[FILENUMBER]). Your question doesn't provide enough information to make a judgement.

Upvotes: 2

Related Questions