# How to create bar or pie chart from sql query where legend automatically uses list [[2]] element

This is my query:

totalCntLByCnty =    {#[[1]], #[[2]] } & /@ SQLExecute[conn, "
SELECT  COUNT(*), c.description
FROM name_loc f
INNER JOIN countries       c ON f.fk_cnty_id  = c.id
INNER JOIN identifiers     i ON f.fk_ident_id = i.id
GROUP BY f.fk_cnty_id
LIMIT 5
"]


I get this:

I got: {{2, "ANDORRA"}, {33, "UNITED ARAB EMIRATES"}, {14, "ANGUILLA"}, {1, "ARMENIA"}, {78, "ARGENTINA"}}

Normally I would not use 'limit 5' I'm using it now to limit the return for this question.

I need to build a barchart where the name of the country returned is automatically used as a legend at the bottom of the chart, and count (vertical axis) is just 'Total's'.

I do not or can not use something like:

ChartLegends -> Placed[{"ANDORRA", "UNITED ARAB EMIRATES", "ANGUILLA",
"ARMENIA", "ARGENTINA"}, Bottom]


Because the name of the countries will change depending upon certain search criteria.

I will be using either a bar chart or a pie chart to display the data.

My question is:

1) How can I generate the chart so the legend uses the name returned from the "#[[2]] list?

2) Is there a better chart or way of displaying this data?

I am using Mathematica 8

Thank you for any help.

-
are you interesting in something like: BarChart[#[[All, 1]], ChartLegends -> Placed[#[[All, 2]], Bottom]] &@data where data is your results vector? Also, maybe you might want to look at SQLSelect to get the data (I usually do it that way and then Tally the results), but seems you know more about SQL than I do –  Pinguin Dirk Jun 1 '13 at 9:41
This question is a continuation of what was posted on Stackoverflow: stackoverflow.com/questions/16860352/… –  Pickett Jun 2 '13 at 1:23

First of all,

{#[[1]], #[[2]] } & /@


is superfluous. It's basically taking apart every sublist and reconstructing them again. Your data can equivalently be retrieved like this:

totalCntLByCnty = SQLExecute[conn, "
SELECT  COUNT(*), c.description
FROM name_loc f
INNER JOIN countries       c ON f.fk_cnty_id  = c.id
INNER JOIN identifiers     i ON f.fk_ident_id = i.id
GROUP BY f.fk_cnty_id
LIMIT 5
"]


Secondly, I think ChartLabels is more appropriate than ChartLegends here. It's your choice in the end, but I will work with ChartLabels.

To understand the syntax we'll be using with BarChart, execute:

BarChart[{1, 2, 3}, ChartLabels -> {"Country A", "Country B", Rotate["Country C", Pi/3]}]


The name of the last country is tilted slightly, this is just in case you have a lot of countries in your chart and it gets crowded, in that case tilting the labels will save you some space. Since it will be easier for you to remove the tilt effect than adding it, I will use tilted labels.

As you can see, the syntax for BarChart effectively requires two lists, one with the countries and one with the corresponding values. To extract these lists, I would suggest a combination of Transpose and Part[expr,n] (also written expr[[n]]).

E.g.

Transpose[{{2, "ANDORRA"}, {33, "UNITED ARAB EMIRATES"}, {14,
"ANGUILLA"}, {1, "ARMENIA"}, {78, "ARGENTINA"}}][[2]]


Yields

{"ANDORRA", "UNITED ARAB EMIRATES", "ANGUILLA", "ARMENIA", "ARGENTINA"}

Therefore, what you're looking for is this piece of code:

BarChart[
Transpose[totalCntLByCnty][[1]],
ChartLabels -> (Rotate[#, Pi/3] & /@
Transpose[totalCntLByCnty][[2]])
]


Perhaps you would also like to add different coloring to each bar to make them more distinct. You can find examples of this in the documentation. You'll simply add the ChartStyle option.

-
Thank you for your excellent post however, using your code I got an error "" has no opening "[". I removed the last brace ']' and I was able to see the bar chart, however there were no labels under the chart. Any ideas? –  sente Jun 2 '13 at 2:46
Yeah, sorry about that. Operator precedence! I had to put parenthesis around the expression for the chart labels, otherwise it wouldn't do the actual work there. Updated the code, also removed the extra ]. –  Pickett Jun 2 '13 at 10:10
You can streamline your code like this: BarChart[#, ChartLabels -> (Rotate[#, Pi/3] & /@ #2)] & @@ Transpose[totalCntLByCnty] -- here the data is only transposed once. –  Mr.Wizard Jun 2 '13 at 12:09
Thank you both for your help, it is most appreciated! –  sente Jun 3 '13 at 1:08