SQL groupby rollup vs union

Question

Sql question:

enter image description here competitor country Acme Corp USA GLOBEX USA Openmedia France K-bam USA Hatdrill UK Hexgreen Germany D-ranron France Faxla Spain the output should be

country competitors France 2 Germany 1 Spain 1 UK 1 USA 3 Total: 8 except using groupby with rollup, i am trying to solve it via "union" but turns out "order by is not functioning" (supposed to order by country name, but my output turns out to "order by competitors" ...) This is my code:

(select country, count(competitor) as competitors
    from table
    group by 1
    order by 1 
    )
    union all
    (select "Total:" as country, count(*) as competitors from table);

Any help would be appreciated! Thank you!

You have to put an ORDER BY at the end to order the final UNION ALL result. — jarlh, Sep 24 '20 at 20:29
Most people here want sample table data and expected result as formatted text, i.e. no images. — jarlh, Sep 24 '20 at 20:30

score 0 · Accepted Answer · answered Sep 24 '20 at 20:30

0

If you want the result ordered, you need an order by after the union:

(select country, count(competitor) as competitors
 from table
 group by 1
) union all
(select 'Total:' as country, count(*) as competitors
 from table
)
order by (country = 'Total:') desc, country asc

answered Sep 24 '20 at 20:30

Gordon Linoff

1,242,037
58
646
786

This is brilliant!! thanks a lot, could you please explain why order by (country = "total") asc can make the "total:" row at the bottom? I'm still kinda confused at it – Sheilalele Sep 24 '20 at 20:47
@Sheilalele . . . Booleans are treated as numbers in such contexts, with "true" as `1` and "false" as `0`. So the `order by` puts the true values last (because of the `desc`). – Gordon Linoff Sep 25 '20 at 00:26

SQL groupby rollup vs union

1 Answers1