-2
SELECT "STORE VISIT",STORE,USERVISITED 
FROM ECRSURVEY 
WHERE "STORE VISIT" > '2014-01-01     00:00:00.000'
GROUP BY STORE, "STORE VISIT", UserVisited

Shouldn't this return a distinct grouping of the column STORE? My result is multiple entries of the same STORE.

Ryan
  • 26,884
  • 9
  • 56
  • 83
Steve Weaver
  • 342
  • 1
  • 4
  • 18

1 Answers1

1

To get distinct STORE:

SELECT STORE 
FROM   ECRSURVEY 
WHERE  "STORE VISIT" > '20140101'
GROUP BY STORE

or

SELECT DISTINCT STORE 
FROM   ECRSURVEY 
WHERE  "STORE VISIT" > '20140101'

or

if you do aggregates of the other two fields then you should get unique rows

SELECT STORE 
       NumStoreVisit = COUNT("STORE VISIT"), 
       NumUserVisited = COUNT(UserVisited)
FROM   ECRSURVEY 
WHERE  "STORE VISIT" > '20140101'
GROUP BY STORE

but if your data is structured like the following then you are asking for the impossible:

enter image description here

whytheq
  • 34,466
  • 65
  • 172
  • 267
  • If I do this I cant get the data for the other two columns that I need. Only the store column is returned. I need all three. – Steve Weaver Feb 06 '14 at 20:36
  • If I try to only group by store, I receive an error because the other two fields are not in the group by clause. – Steve Weaver Feb 06 '14 at 20:48
  • are you multiple rows returned totally identical? or do they have different values in "STORE VISIT" & USERVISITED ? – whytheq Feb 06 '14 at 20:55
  • whytheq is right with his options when trying to get a distinct store. If you are trying to get a distinct store, with data as shown in whytheq's impossible table, do you want the other values to be comma separated perhaps? If yes, have a look at the answer in: http://stackoverflow.com/questions/18235693/comma-delimited-list-as-a-single-string-t-sql – Contisma Feb 07 '14 at 01:39