CODEWITHSUNDEEP
mysqlselectdistinct
Hjalmar
Hjalmar

Reputation: 1941

MySQL Query for fetching multiple distinct values

id  qid answer  date                    answer_userKey
72  2   2       2012-07-30 00:00:00     1
71  1   4       2012-07-30 00:00:00     1
70  2   2       2012-07-30 00:00:00     2
69  1   4       2012-07-30 00:00:00     2
68  2   2       2012-07-30 00:00:00     3
67  1   3       2012-07-30 00:00:00     3
66  2   2       2012-07-31 00:00:00     4
65  1   4       2012-07-31 00:00:00     4
64  2   2       2012-07-31 00:00:00     5

Here's my sample table, I need to get all data + all distinct answer_userKeys for every date like this

date           DISTINCT(answer_userKey)
2012-07-30     3
2012-07-31     2

As well as all the single values in a normal associative array like you get when you do 

SELECT * FROM tbl_data 
WHERE date BETWEEN '2012-07-29' AND '2012-08-01'

Tried everything here :(

Upvotes: 2

Views: 582

Answers (3)

Mike Brant
Mike Brant

Reputation: 71384

If you need both the detail and aggregate data, then you will likely either need to perform two queries to get these two different sets of data, or simply query the details and build the aggregation within whatever language you are using (perhaps in a multidimensional array). For example in pseudo-code:

Query: SELECT * FROM tbl_data WHERE date BETWEEN '?' AND '?'

array; // multidimensional associative array to be populated
while ([fetch next row from result set as row] ) {
    array[row['date']][row['answer_userKey']] = row
}

Upvotes: 0

Saharsh Shah
Saharsh Shah

Reputation: 29051

Try this: 

SELECT DATE(date) dte, COUNT(DISTINCT answer_userKey) cnt
FROM tbl_data 
WHERE DATE(date) BETWEEN '2012-07-29' AND '2012-08-01'
GROUP BY dte;

Upvotes: 1

GKV
GKV

Reputation: 501

i hope this is your requirement..this one works in oracle

select to_char(date,'yyyy-mm-dd') date
      ,count(distinct answer_userKey) DISTINCT(answer_userKey) 
from table_name 
group by to_char(date,'yyyy-mm-dd')

Upvotes: 0

Related Questions