ok so i am doin a table that has info on all 44 presidents. i have to show the state the produces the biggest number of presidents...i am not sure how to do it. here is a lil bit of the table for you . I want it to show that VA has the most presidents from it. help please

| id | name | middle | last | bdate | ddate | yr_sworn | yr_left | party | home_town | state | vp |
+----+------------+----------------+------------+----------+----------+----------+---------+---------------------+----------------+-------+-------------------------------------+
| 1 | George | n/a | Washington | 17320222 | 17991214 | 1789 | 1796 | Federalist | Westmoreland | Va | John Adams |
| 2 | John | n/a | Adams | 17351030 | 18260702 | 1797 | 1801 | Federalist | Quincy | Ma | Thomas Jefferson |
| 5 | James | n/a | Monroe | 17580428 | 18310704 | 1817 | 1825 | Democrat-Republican | Westmoreland | Va | Daniel D Tompkins |
| 3 | Thomas | n/a | Jefferson | 17430413 | 18260804 | 1801 | 1809 | Democrat-Republican | Shadwell | Va | Aaron Burr, George Clinton |
| 4 | James | n/a | Madison | 17510316 | 18360728 | 1809 | 1817 | Democrat-Republican | Port Conway | Va | George Clinton,Elbrige Gerry |
| 6 | John | Quincy | Adams | 17670711 | 18480223 | 1825 | 1829 | Democrat-Republican | Quincy | Ma | John C Calhoun |

Recommended Answers

All 2 Replies

also how would i be able to insert a row with a different number between two rows?

You should be able to do a group by on the state and then select the max of the counts of names.

It'd be something like:

select max(totalnames) as highest_total from ( select state, count(names) as totalnames from t3 group by state) as temptable;
Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.