How can I duplicate columns

Question

I need to return the Cartesian product of this query and a new column that calculates the average of the count columns.

select site_topics.description as topic,
site_subscriptions.site_user_id as user1,
count(*) as count1 
from (
    (site_topics_to_subscriptions
    inner join site_subscriptions 
    on site_subscriptions.id = site_subscription_id)
    inner join site_topics
    on site_topics.id = site_topics_to_subscriptions.topic_id
    )
group by user1,topic
order by user1, count1 desc;

Current results:

-----------------------------------
topic       |user1      |count1
-----------------------------------
Gaming      |1          |3
Photography |1          |3
Art         |1          |1
Gaming      |2          |2
Photography |2          |1
Art         |2          |1

Desired output

-----------------------------------------------------
topic       |user1  |count1 |user2  |count2 |Average
-----------------------------------------------------
Gaming      |1      |3      |2      |2      |0.66
Photography |1      |3      |2      |1      |0.33
Art         |1      |1      |2      |0      |0
Gaming      |2      |2      |1      |3      |0.66
Photography |2      |1      |1      |3      |0.33

'Average' a column that vidies the smaller count by the lager count of that row. Also the same rows are not paired with each other. ie there won't be a row that has user1=1 and user2=1

You asked the same question 20 minutes ago: http://stackoverflow.com/questions/43378689/how-can-i-uplicate-columns — Barmar, Apr 12 '17 at 20:26
@Barmar how is this a duplicate of that question? this is different question please read both and stop marking it as duplicate — 124697, Apr 12 '17 at 20:28
You're trying to move users from rows into columns. That's what pivoting is. — Barmar, Apr 12 '17 at 20:30

Barmar · Accepted Answer · 2017-04-13T14:34:12.420

1

The general structure should be:

SELECT q1.user AS user1, q1.count AS count1, q2.user AS user2, q2.count AS count2, GREATEST(q1.count, q2.count)/LEAST(q1.count, q2.count) AS average
FROM (your query here) AS q1
JOIN (your query here) AS q2
ON q1.user < q2.user AND q1.topic = q2.topic

Then put your query that gets the original table in place of your query here. It might be helpful to create a view that performs the original query, so you don't have to repeat the whole thing in the cross-product.

edited Apr 13 '17 at 14:34

answered Apr 12 '17 at 20:34

Barmar

741,623
53
500
612

I had to change the first `q2.count` to `q1.count` and all where q1.topic = q2.topic; and it works perfectly. I will make the original query into a view as well. thanks very much for the answer – 124697 Apr 13 '17 at 09:52

How can I duplicate columns

1 Answers1