2

I have the following table in 'WITH':

"A" ---- 1 ---- 313
"B" ---- 1 ---- 8
"C" ---- 1 ---- 234

"A" ---- 2 ---- 373
"B" ---- 2 ---- 500
"C" ---- 2 ---- 15

I need to find the variable(A/B/C) in each group(1/2) with the biggest count.

That is, for group 1 the variable is A. For group 2 the variable is B. So the new table I want to be able to make out of it is this:

"A" -- 1
"B" -- 1
"C" -- 0
BVtp
  • 2,308
  • 2
  • 29
  • 68

2 Answers2

0
with x as 
(select row_number() over(partition by groupid order by value desc) as rn, 
 * from tablename)
select t.var, sum(case when t.var = 'A' and y.rn = 1 then 1 
                     when t.var = 'B'and y.rn = 1 then 1
                     when t.var = 'C' and y.rn = 1 then 1
                     else 0 end) as cnt
from tablename t left join y
on y.var = t.var and y.groupid = t.groupid
group by t.var

Using a row_number() to select the max value per group and then summing it up.

Vamsi Prabhala
  • 48,685
  • 4
  • 36
  • 58
  • Thank you. I'm sorry , I'm having a trouble to understand how to integrate it with the my table. The table I described is already in WITH: `with a(nm, bl, mx) as ( select pname,ballotBox.bno, MAX(nofvotes) ..... )` – BVtp Aug 03 '15 at 18:59
  • You can add this as the next cte and then select what you need at the end. like `with as (select ....) , b as (...) select ..from a join b ...` – Vamsi Prabhala Aug 03 '15 at 19:01
  • Not sure I understand what you mean – BVtp Aug 03 '15 at 19:05
  • the table you have currently is built from a `cte`. You can use recursive common table expressions to achieve what you need. Read about `cte` here : http://www.postgresql.org/docs/9.1/static/queries-with.html – Vamsi Prabhala Aug 03 '15 at 19:54
0

There are two problems going on here:

  1. for each group, find which variable had the highest count
  2. count the number of times each variable has the highest count

The way to solve this is to solve each of the problems individually. For 1, you can use a solution derived from this answer:

SELECT t1.*
FROM mytable t1
  LEFT OUTER JOIN mytable t2
    ON (t1.groupid = t2.groupid AND t1.countnum < t2.countnum)

Combining the above call with a printout of the counts of the variables results in the following call:

SELECT variable, count(variable) FROM (
  SELECT t1.*
  FROM mytable t1
    LEFT OUTER JOIN mytable t2
      ON (t1.groupid = t2.groupid AND t1.countnum < t2.countnum)
) AS counts
GROUP BY variable;
Community
  • 1
  • 1
Dan Fu
  • 1
  • Your first query is missing the bit that filters out all the rows you don't want. Your second query is missing the same, plus (once that's fixed) it will not include rows where the count is zero. – ruakh Aug 05 '15 at 07:56