CODEWITHSUNDEEP
sql
Chris Manko
Chris Manko

Reputation: 202

Getting distinct to work

i am using this

SELECT TOP (100) PERCENT 
       CONVERT(char(10), [Reg Date1], 103) AS [Reg Date], 
       Regs
FROM   (SELECT CAST(SetupDateTime AS datetime) AS [Reg Date1], 
               COUNT(DISTINCT ID) AS Regs
        FROM  dbo.tbl_User
        WHERE (CAST(SetupDateTime AS datetime) BETWEEN 
                   CAST(DATEADD(dd, - 7, GETDATE()) AS datetime) AND 
                   CAST(DATEADD(dd, - 1, GETDATE()) AS datetime))
        GROUP BY CAST(SetupDateTime AS datetime)) AS a
ORDER BY [Reg Date1]

which then produces

Reg Date    Regs
07/12/2011  1
07/12/2011  1
07/12/2011  1
08/12/2011  1
08/12/2011  1
09/12/2011  1
09/12/2011  1
10/12/2011  1
10/12/2011  1
10/12/2011  1

but i want it to do 

Reg Date    Regs
07/12/2011  10
08/12/2011  12
09/12/2011  15
10/12/2011  11
11/12/2011  10
12/12/2011  17

i cant seem to get it to group in this way

Upvotes: 1

Views: 89

Answers (2)

aF.
aF.

Reputation: 66757

Try this:

SELECT     TOP (100) PERCENT CONVERT(char(10), [Reg Date1], 103) AS [Reg Date], Sum(Regs)
FROM         (SELECT     CAST(SetupDateTime AS datetime) AS [Reg Date1], COUNT(DISTINCT ID) AS Regs
                       FROM          dbo.tbl_User
                       WHERE      (CAST(SetupDateTime AS datetime) BETWEEN CAST(DATEADD(dd, - 7, CAST(convert(varchar, GETDATE(), 112) AS datetime)) AS datetime) AND CAST(convert(varchar, GETDATE(), 112) 
                                              AS datetime))
      GROUP BY CAST(SetupDateTime AS datetime)) AS a
group by CONVERT(char(10), [Reg Date1], 103)
ORDER BY CONVERT(char(10), [Reg Date1], 103)

Upvotes: 2

Barry Kaye
Barry Kaye

Reputation: 7759

Try this:

SELECT TOP (100) PERCENT CONVERT(char(10), [Reg Date1], 103) AS [Reg Date], COUNT(Regs)

Note the use of COUNT which is I suspect what you are trying to do.

Upvotes: 0

Related Questions