Not able to understand how to write group by

On Wednesday, July 02, 2014 02:38:36 PM jared wrote:

you have:
GROUP BY users.gender, measures.option

instead try:
GROUP BY users

*group by* on full table(*users*). I am away from our production DB. Could you
tell me how this little change will solve the whole problem and help me to get
the data as per the format I am looking for.

--
================
Regards,
Arup Rakshit
================
Debugging is twice as hard as writing the code in the first place. Therefore,
if you write the code as cleverly as possible, you are, by definition, not
smart enough to debug it.

--Brian Kernighan

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

On Wednesday, July 02, 2014 02:49:54 PM you wrote:

On Wed, Jul 2, 2014 at 1:44 PM, Arup Rakshit <aruprakshit@rocketmail.com>

wrote:

*group by* on full table(*users*). I am away from our production DB. Could
you
tell me how this little change will solve the whole problem and help me to
get
the data as per the format I am looking for.

Arup,
I meant:
GROUP BY users.gender

That makes sense. How then calculate the average value for 2 different answers
of the given *daily_action_id* ? As I said *answer1* and *answer2*....

--
================
Regards,
Arup Rakshit
================
Debugging is twice as hard as writing the code in the first place. Therefore,
if you write the code as cleverly as possible, you are, by definition, not
smart enough to debug it.

--Brian Kernighan

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

you have:
GROUP BY users.gender, measures.option

instead try:
GROUP BY users

On Wed, Jul 2, 2014 at 12:55 PM, Arup Rakshit <aruprakshit@rocketmail.com>
wrote:

On Wednesday, July 02, 2014 12:28:04 PM David G Johnston wrote:

Steve Crawford wrote

Are you sure this is what you want?

Since there are two columns you will have to either use a CASE or a select
to facilitate calculating the values for each of the columns.

SELECT gender, answer1_avg, answer2_avg
FROM (SELECT DISTINCT gender FROM ...) gn
LEFT JOIN (SELECT gender, answer1_avg FROM ...) ans1 USING (gender)
LEFT JOIN (SELECT gender, answer2_avg FROM ...) ans2 USING (gender)

You could also try learning "crosstab" from the "tablefunc" extension:

http://www.postgresql.org/docs/9.3/interactive/tablefunc.html

I do not see how a single "participant count" column will provide a
meaningful piece of data...

David J.

This is a summary report of a specific questions answers gender wise.

Q is "How much you learned today?"

how many female participants in answering the question Q. If they answers,
then average of A1 and average of A2 ( A1. A2 means two types of answers).
They put the numbers in those 2 types. They are allowed to choose either of
the answer type, but not the both. So, if any female F1 provides 80 to A2, in
that day, she wouldn't be allowed to answer for A1.

Same stands for male and <non-gender> also.

--
================
Regards,
Arup Rakshit
================
Debugging is twice as hard as writing the code in the first place. Therefore,
if you write the code as cleverly as possible, you are, by definition, not
smart enough to debug it.

--Brian Kernighan

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

On Wednesday, July 02, 2014 12:28:04 PM David G Johnston wrote:

Steve Crawford wrote

Are you sure this is what you want?

Since there are two columns you will have to either use a CASE or a select
to facilitate calculating the values for each of the columns.

SELECT gender, answer1_avg, answer2_avg
FROM (SELECT DISTINCT gender FROM ...) gn
LEFT JOIN (SELECT gender, answer1_avg FROM ...) ans1 USING (gender)
LEFT JOIN (SELECT gender, answer2_avg FROM ...) ans2 USING (gender)

You could also try learning "crosstab" from the "tablefunc" extension:

http://www.postgresql.org/docs/9.3/interactive/tablefunc.html

I do not see how a single "participant count" column will provide a
meaningful piece of data...

David J.

This is a summary report of a specific questions answers gender wise.

Q is "How much you learned today?"

how many female participants in answering the question Q. If they answers,
then average of A1 and average of A2 ( A1. A2 means two types of answers).
They put the numbers in those 2 types. They are allowed to choose either of
the answer type, but not the both. So, if any female F1 provides 80 to A2, in
that day, she wouldn't be allowed to answer for A1.

Same stands for male and <non-gender> also.

--
================
Regards,
Arup Rakshit
================
Debugging is twice as hard as writing the code in the first place. Therefore,
if you write the code as cleverly as possible, you are, by definition, not
smart enough to debug it.

--Brian Kernighan

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

On Wed, Jul 2, 2014 at 1:44 PM, Arup Rakshit <aruprakshit@rocketmail.com>
wrote:

*group by* on full table(*users*). I am away from our production DB. Could
you
tell me how this little change will solve the whole problem and help me to
get
the data as per the format I am looking for.

Arup,
I meant:
GROUP BY users.gender

On 7/2/2014 10:44 AM, Arup Rakshit wrote:

*group by* on full table(*users*). I am away from our production DB. Could you
tell me how this little change will solve the whole problem and help me to get
the data as per the format I am looking for.

I believe he meant

group by users.gender

--
john r pierce 37N 122W
somewhere on the middle of the left coast

On 07/02/2014 09:55 AM, Arup Rakshit wrote:

SELECT users.gender,count(*) as participant,avg(daily_action_answers.value) as
value
FROM "users" INNER JOIN "daily_action_answers" ON
"daily_action_answers"."user_id" = "users"."id"
INNER JOIN "measures" ON "measures"."id" = "daily_action_answers"."measure_id"
WHERE (((daily_action_answers.day between now() and <last_date_of_year>) and
daily_action_answers.daily_action_id = 1))
GROUP BY users.gender, measures.option

This is producing the below

gender | participants | value
n 2 12
n 1 3
m 1 4
m 4 12
f 3 23
f 4 15

Here n.m,f it comes 2 times, because the possible answer is 2. That's the
problem with my current query. I don't understand which average value for
which answer.

Can we make the output as below ?

gender participants answer1_avg answer2_avg
n 3 12 3
m 5 4 12
f 7 15 23

As mentioned by jared, the problem is the additional group by
measures.option which needs to be eliminated. To better understand what
is happening, just add measures.option to your list of output columns.
Right now the grouping is hidden because you aren't showing that column.

Cheers,
Steve

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

afonit wrote

gender participants answer1_avg answer2_avg
n 3 12 3
m 5 4 12
f 7 15 23

Are you sure this is what you want?

Since there are two columns you will have to either use a CASE or a
sub-select to facilitate calculating the values for each of the columns.

SELECT gender, answer1_avg, answer2_avg
FROM (SELECT DISTINCT gender FROM ...) gn
LEFT JOIN (SELECT gender, answer1_avg FROM ...) ans1 USING (gender)
LEFT JOIN (SELECT gender, answer2_avg FROM ...) ans2 USING (gender)

You could also try learning "crosstab" from the "tablefunc" extension:

http://www.postgresql.org/docs/9.3/interactive/tablefunc.html

I do not see how a single "participant count" column will provide a
meaningful piece of data...

David J.

--
View this message in context: http://postgresql.1045698.n5.nabble.com/Not-able-to-understand-how-to-write-group-by-tp5810250p5810279.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Steve Crawford wrote

On 07/02/2014 09:55 AM, Arup Rakshit wrote:

SELECT users.gender,count(*) as
participant,avg(daily_action_answers.value) as
value
FROM "users" INNER JOIN "daily_action_answers" ON
"daily_action_answers"."user_id" = "users"."id"
INNER JOIN "measures" ON "measures"."id" =
"daily_action_answers"."measure_id"
WHERE (((daily_action_answers.day between now() and

<last_date_of_year>
) and

daily_action_answers.daily_action_id = 1))
GROUP BY users.gender, measures.option

This is producing the below

gender | participants | value
n 2 12
n 1 3
m 1 4
m 4 12
f 3 23
f 4 15

Here n.m,f it comes 2 times, because the possible answer is 2. That's the
problem with my current query. I don't understand which average value for
which answer.

Can we make the output as below ?

gender participants answer1_avg answer2_avg
n 3 12 3
m 5 4 12
f 7 15 23

As mentioned by jared, the problem is the additional group by
measures.option which needs to be eliminated. To better understand what
is happening, just add measures.option to your list of output columns.
Right now the grouping is hidden because you aren't showing that column.

Are you sure this is what you want?

Since there are two columns you will have to either use a CASE or a select
to facilitate calculating the values for each of the columns.

SELECT gender, answer1_avg, answer2_avg
FROM (SELECT DISTINCT gender FROM ...) gn
LEFT JOIN (SELECT gender, answer1_avg FROM ...) ans1 USING (gender)
LEFT JOIN (SELECT gender, answer2_avg FROM ...) ans2 USING (gender)

You could also try learning "crosstab" from the "tablefunc" extension:

http://www.postgresql.org/docs/9.3/interactive/tablefunc.html

I do not see how a single "participant count" column will provide a
meaningful piece of data...

David J.

--
View this message in context: http://postgresql.1045698.n5.nabble.com/Not-able-to-understand-how-to-write-group-by-tp5810250p5810283.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Are you sure this is what you want?

Since there are two columns you will have to either use a CASE or a
sub-select to facilitate calculating the values for each of the columns.

SELECT gender, answer1_avg, answer2_avg
FROM (SELECT DISTINCT gender FROM ...) gn
LEFT JOIN (SELECT gender, answer1_avg FROM ...) ans1 USING (gender)
LEFT JOIN (SELECT gender, answer2_avg FROM ...) ans2 USING (gender)

You could also try learning "crosstab" from the "tablefunc" extension:

http://www.postgresql.org/docs/9.3/interactive/tablefunc.html

I do not see how a single "participant count" column will provide a
meaningful piece of data...

David J.

Finally I wrote 

SELECT users.gender,count(*) as participant,
case when daily_action_answers.measure_id = 1 then avg(daily_action_answers.value) end as cpd,
case when daily_action_answers.measure_id = 2 then avg(daily_action_answers.value) end as other 
FROM users INNER JOIN daily_action_answers ON daily_action_answers.user_id = users.id
INNER JOIN measures ON measures.id = daily_action_answers.measure_id
WHERE (((daily_action_answers.day between '2014-07-03' and '2014-12-31')and daily_action_answers.daily_action_id = 1)) 
GROUP BY users.gender, daily_action_answers.measure_id

gender | participant | cpd |other 

"Female", 2 , 8.5, 0.0
"Female", 1 , 0.0, 8.0
"None", 2, 6.5, 0.0
"None", 1, 0.0, 5.0
"Male", 1, 4.0, 0.0, 
"Male", 2, 0.0, 10.0

Problem is.. I am not able to merge those pair rows into one... :-(

On 02 Jul 2014, at 18:55, Arup Rakshit <aruprakshit@rocketmail.com> wrote:

Hi,

I am working on web development project. There I am using this awesome DB. Let
me tell you first the schema that I am having associated the problem.

I am having a table *users* - which has many fields. Out of them, the one I
need here is *gender*. This column can have value "f"/"m"/"n".

I have a table called *measures*. This table contains all possible answers of
questions lies in the table called *daily_actions*. It has a foreign key
columns as *daily_action_id*.

I have a table called *daily_actions*. It has a field *question* and several
other fields too.

I have a table called *daily_action_answers*. It has foreign keys called
"user_id", "daily_action_id" and "measure_id". Another field is *value* and
"day". *day* is a _date_ field.

SELECT users.gender,count(*) as participant,avg(daily_action_answers.value) as
value
FROM "users" INNER JOIN "daily_action_answers" ON
"daily_action_answers"."user_id" = "users"."id"
INNER JOIN "measures" ON "measures"."id" = "daily_action_answers"."measure_id"
WHERE (((daily_action_answers.day between now() and <last_date_of_year>) and
daily_action_answers.daily_action_id = 1))
GROUP BY users.gender, measures.option

Can we make the output as below ?

gender participants answer1_avg answer2_avg
n 3 12 3
m 5 4 12
f 7 15 23

Following the discussion, if this is really only about a fixed number of measures you can solve that by using the CASE statement for each measure involved and the fact that aggregate functions skip NULL-values, like so:

SELECT users.gender,count(*) as participant,
avg(CASE WHEN measures.id = 1 THEN daily_action_answers.value ELSE NULL END) as value1,
avg(CASE WHEN measures.id = 2 THEN daily_action_answers.value ELSE NULL END) as value2

FROM users INNER JOIN daily_action_answers ON daily_action_answers.user_id = users.id
INNER JOIN measures ON measures.id = daily_action_answers.measure_id
WHERE (((daily_action_answers.day between now() and <last_date_of_year>) and daily_action_answers.daily_action_id = 1))
GROUP BY users.gender

BTW, I noticed you are mixing how you quote the same identifiers. Quoting identifiers makes them case-sensitive, so either always quote them or never quote them, but don’t mix or you’ll get into trouble if you ever end up in a database(-version) where identifiers are case-folded to upper case (which is pretty much any database different from PG).

Alban Hertroys
--
If you can't see the forest for the trees,
cut the trees and you'll find there is no forest.

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Are you sure this is what you want?

Since there are two columns you will have to either use a CASE or a
<sub-select> to facilitate calculating the values for each of the columns.

SELECT gender, answer1_avg, answer2_avg
FROM (SELECT DISTINCT gender FROM ...) gn
LEFT JOIN (SELECT gender, answer1_avg FROM ...) ans1 USING (gender)
LEFT JOIN (SELECT gender, answer2_avg FROM ...) ans2 USING (gender)

You could also try learning "crosstab" from the "tablefunc" extension:

http://www.postgresql.org/docs/9.3/interactive/tablefunc.html

I do not see how a single "participant count" column will provide a
meaningful piece of data...

Finally I wrote 

SELECT users.gender,count(*) as participant,
case when daily_action_answers.measure_id = 1 then avg(daily_action_answers.value) end as cpd,
case when daily_action_answers.measure_id = 2 then avg(daily_action_answers.value) end as other 
FROM users INNER JOIN daily_action_answers ON daily_action_answers.user_id = users.id
INNER JOIN measures ON measures.id = daily_action_answers.measure_id
WHERE (((daily_action_answers.day between '2014-07-03' and '2014-12-31')and daily_action_answers.daily_action_id = 1)) 
GROUP BY users.gender, daily_action_answers.measure_id

gender | participant | cpd |other 

"Female", 2 , 8.5, 0.0
"Female", 1 , 0.0, 8.0
"None", 2, 6.5, 0.0
"None", 1, 0.0, 5.0
"Male", 1, 4.0, 0.0, 
"Male", 2, 0.0, 10.0

Problem is.. I am not able to merge those pair rows into one... :-(

Arup Rakshit wrote

SELECT users.gender,count(*) as participant,
case when daily_action_answers.measure_id = 1 then
avg(daily_action_answers.value) end as cpd,
case when daily_action_answers.measure_id = 2 then
avg(daily_action_answers.value) end as other 
FROM users INNER JOIN daily_action_answers ON daily_action_answers.user_id
= users.id
INNER JOIN measures ON measures.id = daily_action_answers.measure_id
WHERE (((daily_action_answers.day between '2014-07-03' and
'2014-12-31')and daily_action_answers.daily_action_id = 1)) 
GROUP BY users.gender, daily_action_answers.measure_id

gender | participant | cpd |other 

"Female", 2 , 8.5, 0.0
"Female", 1 , 0.0, 8.0
"None", 2, 6.5, 0.0
"None", 1, 0.0, 5.0
"Male", 1, 4.0, 0.0, 
"Male", 2, 0.0, 10.0

Problem is.. I am not able to merge those pair rows into one... :-(

Without commenting on the rest of it...to combine what you show here just
GROUP BY gender and SUM() everything else (i.e., turn the above into a
subquery and then do this)

David J.

--
View this message in context: http://postgresql.1045698.n5.nabble.com/Not-able-to-understand-how-to-write-group-by-tp5810250p5810365.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

unsubscribe pgsql-general@postgresql.org

Without commenting on the rest of it...to combine what you show here just
GROUP BY gender and SUM() everything else (i.e., turn the above into a
subquery and then do this)

David J.

Exactly.. I am done. Here is the ORM query :-

  def self.employee_learning_by_gender(question_id)
    cpd_id = Measure.find_by(option: 'CPD').id
    other_id = Measure.find_by(option: 'Others').id
   
    User.select("view.gender, sum(view.participant) as participant, sum(cpd) as cpd, sum(other) as other").from(User.joins(daily_action_answers: [:measure]).group("users.gender, daily_action_answers.measure_id")
                                                .where("((daily_action_answers.day between ? and ?) and
                                                         daily_action_answers.daily_action_id = ?)",
                                                         Date.today, Date.today.end_of_year,
                                                         question_id
                                                       )
                                                .select("users.gender, count(*) as participant,
                                                         case when daily_action_answers.measure_id = #{cpd_id} then avg(daily_action_answers.value) end as cpd,
                                                         case when daily_action_answers.measure_id = #{other_id} then avg(daily_action_answers.value) end as other"
                                                       ), :view).group("view.gender")
  end

On Thursday, July 03, 2014 09:04:36 AM John R Pierce wrote:

On 7/3/2014 4:01 AM, Arup Rakshit wrote:

Exactly.. I am done. Here is the ORM query :-

OT, but it boggles my mind that anyone thinks thats 'better' than the
straight SQL

I would like to see your idea. Could you please ? My thick brain not able to
produce any straight forward one. Not so good in sql recently.. :(

--
================
Regards,
Arup Rakshit
================
Debugging is twice as hard as writing the code in the first place. Therefore,
if you write the code as cleverly as possible, you are, by definition, not
smart enough to debug it.

--Brian Kernighan

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

On Thursday, July 03, 2014 09:04:36 AM John R Pierce wrote:

On 7/3/2014 4:01 AM, Arup Rakshit wrote:

Exactly.. I am done. Here is the ORM query :-

OT, but it boggles my mind that anyone thinks thats 'better' than the
straight SQL

I would like to see your idea. Could you please ? My thick brain not able to
produce any straight forward one. Not so good in sql recently.. :(

--
================
Regards,
Arup Rakshit
================
Debugging is twice as hard as writing the code in the first place. Therefore,
if you write the code as cleverly as possible, you are, by definition, not
smart enough to debug it.

--Brian Kernighan

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

On 7/3/2014 4:01 AM, Arup Rakshit wrote:

Exactly.. I am done. Here is the ORM query :-

def self.employee_learning_by_gender(question_id)
cpd_id = Measure.find_by(option: 'CPD').id
other_id = Measure.find_by(option: 'Others').id
User.select("view.gender, sum(view.participant) as participant,
sum(cpd) as cpd, sum(other) as
other").from(User.joins(daily_action_answers:
[:measure]).group("users.gender, daily_action_answers.measure_id")
.where("((daily_action_answers.day between ? and ?) and
daily_action_answers.daily_action_id = ?)",
Date.today, Date.today.end_of_year,
question_id
)
.select("users.gender, count(*) as participant,
case when daily_action_answers.measure_id = #{cpd_id}
then avg(daily_action_answers.value) end as cpd,
case when daily_action_answers.measure_id = #{other_id}
then avg(daily_action_answers.value) end as other"
), :view).group("view.gender")
end

OT, but it boggles my mind that anyone thinks thats 'better' than the
straight SQL

--
john r pierce 37N 122W
somewhere on the middle of the left coast

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general