0

I need 5 columns with highest values (values must be sum of each column) for report. I have about 40 contury colunms something like england_turist, polad_turists, us_turists, india_turists... eng - 152(sum), pol - 280(sum), us - 54(sum), in - 36(sum)... and query needs to pick pol - 280 and eng - 152. So query need to sum every column and pick 5 columns with highest values.

I got result from here but how to create query

SELECT * FROM
(
     SELECT 'Bmw' AS vrstaautomobila, SUM(bmw) AS brautomobila FROM unos_korisnika
     UNION
     SELECT 'Mercedes' AS vrstaautomobila, SUM(mercedes) AS brautomobila FROM unos_korisnika
     UNION
     SELECT 'Opel' AS vrstaautomobila, SUM(opel) AS brautomobila FROM unos_korisnika
     UNION
     SELECT 'Nissan' AS vrstaautomobila, SUM(nissan) AS brautomobila FROM unos_korisnika
     UNION
     SELECT 'Peguoet' AS vrstaautomobila, SUM(peguoet) AS brautomobila FROM unos_korisnika
     UNION
     SELECT 'Volkswagen' AS vrstaautomobila, SUM(volkswagen) AS brautomobila FROM unos_korisnika
)
AS results
ORDER BY brautomobila DESC
LIMIT 5
Clay
  • 4,700
  • 3
  • 33
  • 49
Sandi Budic
  • 59
  • 2
  • 10
  • 3
    Holy cow you made a column for each country? Why not make a tourist column and a country column? This is not how SQL is meant to be. – Phiter Oct 20 '16 at 10:05
  • It would be even easier to make your query, using group by, order by, top(5), etc – Phiter Oct 20 '16 at 10:05
  • @PhiterFernandes You are right but do not use this type of language at here – Kumar Oct 20 '16 at 10:06
  • let me show you... i have a Hotel name... every hotel put record of turists income every day of each contury, so need to be columns. so i need now which contury have best income and later i will need top 5 hotels. http://prntscr.com/cwm1ah – Sandi Budic Oct 20 '16 at 10:23
  • @PhiterFernandes has a point, but I know things are as they are, not always our choice. In your case I would take a look in this question, might help: http://stackoverflow.com/questions/16359345/transposing-dynamic-columns-to-rows – Edelmar Ziegler Oct 20 '16 at 12:30
  • @Sandi Budic: You are wrong. The datamodel is very bad. You think you could not insert the daily data in your table if you had a record per country rather than a column per country? No. The only difference would be that you would insert several records instead of only one. No big deal. – Thorsten Kettner Oct 20 '16 at 12:34
  • I know it sucks when people give you a table with an ugly layout, maybe you can make your voice be heard and suggest them to change this structure. Tell them that it's better and will make it faster. – Phiter Oct 20 '16 at 12:39
  • hm can you show me hotel name and some information about hotel and 5 contury work as row... and inserting about 40 different values for this row every day... – Sandi Budic Oct 20 '16 at 19:49

1 Answers1

1

Transpose the table (convert columns to rows) and then select the top values.

select * from transposed_table order by value_column desc limit 5

you may use How to transpose mysql table rows into columns to transpose your data or may be this SQL - How to transpose?

Community
  • 1
  • 1
Danyal Sandeelo
  • 12,196
  • 10
  • 47
  • 78
  • let me show you... i have a Hotel name... every hotel put record of turists income every day of each contury, so need to be columns. so i need now which contury have best income and later i will need top 5 hotels. http://prntscr.com/cwm1ah – Sandi Budic Oct 20 '16 at 10:23
  • is posible to do somethig like `SELECT SUM(at_turista) as a, SUM(bg_turista) as b, SUM(ba_turista) as c, SUM(gr_turista) as d, SUM(dk_turista) as e, SUM(is_turista) as f, SUM(it_turista) as g FROM mytable` and get higher values from a,b,c,d,f,g. – Sandi Budic Oct 20 '16 at 12:22
  • at_turista what is this? – vinay kumar reddy Oct 21 '16 at 13:52
  • SELECT SUM(at_turista) as a, SUM(bg_turista) as b, SUM(ba_turista) as c, SUM(gr_turista) as d, SUM(dk_turista) as e, SUM(is_turista) as f, SUM(it_turista) as g FROM mytable desc limit 5 – vinay kumar reddy Oct 21 '16 at 13:55
  • @vinaykumarreddy at_turista is column name of turists from Austria. – Sandi Budic Oct 21 '16 at 14:01
  • @vinaykumarreddy sql code work in sql... but i dont know how to make query code in php – Sandi Budic Oct 23 '16 at 18:40