CODEWITHSUNDEEP
mysql
thebigrlebowski
thebigrlebowski

Reputation: 71

How to display two tables side by side that are joined?

I'm currently learning MySQL and am working on a query that displays the top 5 and bottom 5 categories and groups by joining 2 tables. What I have meets the requirements but I want to display it more cleanly. I've got this to display by using a union but was wondering if I could show the results as four columns instead for a cleaner look. 2 columns related to the top 5 and 2 related to the bottom five categories determined by the number of groups in each category.

Current query:

  SELECT*
FROM(SELECT
    category_name,
    count(category_name) AS NumOfGroups
From 
    category c
JOIN
    grp g ON c.category_id=g.category_id
GROUP BY category_name
order by NumOfGroups desc
LIMIT 5) most
UNION 
SELECT *
FROM (SELECT
    category_name,
    count(category_name) AS NumOfGroups
From 
    category c
JOIN
    grp g ON c.category_id=g.category_id
GROUP BY category_name
ORDER BY NumOfGroups ASC
LIMIT 5) Least;    

This displays:

    category              NumOfGroups
    Tech                     911
    Food & Drink             790
    Photography              320
    Outdoors & Adventure     218
    Games                    166
    Singles                    4
    Fitness                   15
    Paranormal                16
    Fashion & Beauty          26
    Movements & Politics      32

Can I take this one step further to display a result like below? Would I have to transpose?

Desired result:

category              NumOfGroups    category              NumOfGroups
Tech                   911           Singles                  4
Food & Drink           790           Fitness                 15
Photography            320           Paranormal              16
Outdoors & Adventure   218           Fashion & Beauty        26
Games                  166           Movements & Politics    32

Upvotes: 0

Views: 547

Answers (2)

Eric Shieh
Eric Shieh

Reputation: 817

IMO, this is best done at the application level rather than in your database queries. Using each tool as it's designed results in cleaner solutions. However, if you really need to do this in mysql, you can generate row numbers in each of your subqueries and join them to make a unified result.

set @row:=0;
set @row2:=0;

SELECT most.category_name,most.members,least.category_name,least.members
FROM (
    SELECT *,@row := @row + 1 as rownum 
    FROM (
        SELECT
            category_name,
            count(*) numberOfGroups,
        FROM category c
        JOIN grp g ON c.category_id=g.category_id
        GROUP by category_name
        ORDER BY numberOfGroups DESC
        LIMIT 5
    ) temp
) most
LEFT JOIN (
    SELECT *,@row2 := @row2 + 1 as rownum 
    FROM (
        SELECT 
            category_name,
            count(*) numberOfGroups
        FROM category c
        JOIN grp g ON c.category_id=g.category_id
        GROUP by category_name
        ORDER BY numberOfGroups ASC
        LIMIT 5
    ) temp
) least
ON most.rownum=least.rownum;

There's still a caveat where the "most" subquery needs to always be >= the number of row results relative to "least" or you'll get clipping. As long as it's always 5 though (as it appears to be very likely in your case), you'll be safe.

Upvotes: 0

forpas
forpas

Reputation: 164164

Create a CTE where you use ROW_NUMBER() window function twice to rank the rows based on the value of NumOfGroups and then do a self join:

WITH cte AS (
  SELECT c.category_name, COUNT(*) NumOfGroups,
         ROW_NUMBER() OVER (ORDER BY COUNT(*) DESC) rn_most,
         ROW_NUMBER() OVER (ORDER BY COUNT(*)) rn_least
  FROM category c JOIN grp g 
  ON c.category_id = g.category_id
  GROUP BY c.category_name
)
SELECT c1.category_name category_most, c1.NumOfGroups NumOfGroups_most,
       c2.category_name category_least, c2.NumOfGroups NumOfGroups_least
FROM cte c1 INNER JOIN cte c2
ON c2.rn_least = c1.rn_most
WHERE c1.rn_most <= 5
ORDER BY c2.rn_least

Upvotes: 1

Related Questions