MySQL Query for fetching multiple distinct values

Question

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 :(

Saharsh Shah · Accepted Answer · 2012-12-19 18:45:25Z

1

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;

answered Dec 19, 2012 at 18:45

Saharsh Shah

29.1k9 gold badges53 silver badges85 bronze badges

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

1 Comment

Hjalmar Over a year ago

Solved it :) Just added *, before the DATE(date) and everything worked as a charm! Thank you!

GKV · Accepted Answer · 2012-12-19 18:41:19Z

0

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')

answered Dec 19, 2012 at 18:41

GKV

5014 silver badges8 bronze badges

Comments

Mike Brant · Accepted Answer · 2012-12-19 18:45:35Z

0

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
}

answered Dec 19, 2012 at 18:45

Mike Brant

71.4k10 gold badges102 silver badges104 bronze badges

Collectives™ on Stack Overflow

MySQL Query for fetching multiple distinct values

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related