CREATE TEMPORARY TABLE revenue_tbl
SELECT store_id,
year(order_date) AS order_year,
(order_quantity * unit_price) AS sales,
((order_quantity * unit_cost) + ((order_quantity * unit_price) * discount_applied)) AS cost,
(order_quantity * unit_price) - ((order_quantity * unit_cost) + ((order_quantity * unit_price) * discount_applied)) AS profit
FROM sales_order;5. Geo-location Summary
A temporary table containing sales, cost and profit.
Total sales, cost and profit By :-
SELECT r.region,
round(SUM(rt.sales), 2) AS Total_Sales,
round(SUM(rt.cost), 2) AS Total_Cost,
round(SUM(rt.profit), 2) AS Total_Profit
FROM revenue_tbl rt
JOIN store s
ON rt.store_id = s.store_id
JOIN regions r
ON s.state_code = r.state_code
GROUP BY r.region
ORDER BY Total_Profit DESC;| Region | Total Sales | Total Cost | Total Profit |
|---|---|---|---|
| West | 29,117,275.37 | 21,600,037.62 | 7,517,237.75 |
| South | 26,424,203.68 | 19,580,128.45 | 6,844,075.24 |
| Midwest | 17,524,761.22 | 12,988,219.29 | 4,536,541.93 |
| Northeast | 9,626,486.30 | 7,199,014.32 | 2,427,471.98 |
The highest profit was made in th west region, followed by the South and Midwest, while the Northeast Region was where the lowest profit was made.
SELECT s.state,
round(SUM(rt.sales), 2) AS Total_Sales,
round(SUM(rt.cost), 2) AS Total_Cost,
round(SUM(rt.profit), 2) AS Total_Profit
FROM revenue_tbl rt
JOIN store s
ON rt.store_id = s.store_id
GROUP BY s.state
ORDER BY Total_Profit DESC;| State | Total Sales | Total Cost | Total Profit |
|---|---|---|---|
| California | 17,283,447.28 | 12,928,105.62 | 4,355,341.67 |
| Texas | 8,339,041.10 | 6,214,597.69 | 2,124,443.41 |
| Illinois | 6,543,441.12 | 4,852,767.21 | 1,690,673.91 |
| Florida | 6,087,613.29 | 4,467,219.39 | 1,620,393.90 |
| New York | 4,034,987.91 | 3,014,268.87 | 1,020,719.03 |
| Colorado | 3,265,050.70 | 2,361,928.60 | 903,122.10 |
| Indiana | 3,087,467.20 | 2,260,215.96 | 827,251.24 |
| North Carolina | 2,227,415.00 | 1,629,237.20 | 598,177.80 |
| Washington | 1,899,570.60 | 1,389,664.16 | 509,906.44 |
| Virginia | 1,862,358.80 | 1,361,738.42 | 500,620.38 |
| Arizona | 1,831,532.10 | 1,386,010.81 | 445,521.28 |
| Nevada | 1,696,326.10 | 1,256,277.04 | 440,049.06 |
| Connecticut | 1,800,826.00 | 1,366,710.39 | 434,115.61 |
| New Jersey | 1,589,139.50 | 1,178,288.20 | 410,851.30 |
| Tennessee | 1,418,698.21 | 1,024,309.17 | 394,389.04 |
| Michigan | 1,537,261.40 | 1,144,435.21 | 392,826.19 |
| Ohio | 1,254,635.30 | 893,007.19 | 361,628.11 |
| Oregon | 1,242,133.09 | 884,683.95 | 357,449.14 |
| Georgia | 1,333,132.50 | 999,907.70 | 333,224.79 |
| Oklahoma | 1,165,210.39 | 870,279.55 | 294,930.84 |
| Massachusetts | 1,155,461.90 | 869,672.80 | 285,789.10 |
| Kansas | 1,063,236.40 | 810,644.23 | 252,592.17 |
| Louisiana | 1,084,649.60 | 843,648.93 | 241,000.67 |
| Missouri | 858,946.70 | 645,514.12 | 213,432.59 |
| Utah | 819,510.50 | 606,563.63 | 212,946.87 |
| Alabama | 745,636.30 | 540,553.96 | 205,082.34 |
| Iowa | 800,060.40 | 595,459.25 | 204,601.14 |
| Wisconsin | 660,419.00 | 469,419.46 | 190,999.55 |
| Pennsylvania | 626,068.10 | 442,641.33 | 183,426.77 |
| South Carolina | 724,966.80 | 558,298.68 | 166,668.13 |
| Minnesota | 747,123.70 | 580,931.64 | 166,192.05 |
| New Mexico | 521,507.90 | 364,111.97 | 157,395.93 |
| Maryland | 572,454.70 | 441,439.75 | 131,014.95 |
| Nebraska | 393,450.80 | 282,571.83 | 110,878.97 |
| Kentucky | 382,067.50 | 273,166.37 | 108,901.13 |
| North Dakota | 424,713.00 | 339,483.78 | 85,229.22 |
| District of Columbia | 188,149.40 | 124,393.88 | 63,755.52 |
| Montana | 190,320.20 | 137,820.98 | 52,499.22 |
| New Hampshire | 224,704.60 | 172,854.74 | 51,849.86 |
| Idaho | 209,629.60 | 160,966.16 | 48,663.44 |
| Rhode Island | 195,298.30 | 154,577.98 | 40,720.32 |
| South Dakota | 154,006.20 | 113,769.42 | 40,236.78 |
| Hawaii | 158,247.30 | 123,904.71 | 34,342.59 |
| Mississippi | 178,179.80 | 144,369.59 | 33,810.21 |
| Arkansas | 114,630.30 | 86,968.18 | 27,662.12 |
SELECT s.county,
round(SUM(rt.sales), 2) AS Total_Sales,
round(SUM(rt.cost), 2) AS Total_Cost,
round(SUM(rt.profit), 2) AS Total_Profit
FROM revenue_tbl rt
JOIN store s
ON rt.store_id = s.store_id
GROUP BY s.county
ORDER BY Total_Profit DESC;| County | Total Sales | Total Cost | Total Profit |
|---|---|---|---|
| Los Angeles County | 3,705,528.80 | 2,874,450.23 | 831,078.57 |
| Orange County | 2,291,601.00 | 1,710,272.73 | 581,328.27 |
| San Diego County | 2,217,438.70 | 1,646,807.24 | 570,631.46 |
| Cook County | 2,114,332.41 | 1,570,701.44 | 543,630.97 |
| Clark County | 1,737,048.70 | 1,259,648.31 | 477,400.39 |
| Broward County | 1,690,858.90 | 1,230,316.72 | 460,542.18 |
| Marion County | 1,435,073.00 | 1,041,745.02 | 393,327.99 |
| Maricopa County | 1,574,285.60 | 1,184,173.48 | 390,112.12 |
| Riverside County | 1,361,165.30 | 996,199.05 | 364,966.25 |
| DuPage County | 1,430,523.70 | 1,078,788.34 | 351,735.36 |
| San Bernardino County | 1,243,238.59 | 943,799.02 | 299,439.57 |
| Suffolk County | 1,103,838.40 | 817,496.28 | 286,342.12 |
| Middlesex County | 1,047,122.90 | 772,038.96 | 275,083.94 |
| Miami-Dade County | 815,872.40 | 578,906.03 | 236,966.37 |
| King County | 849,258.50 | 625,324.67 | 223,933.83 |
| Contra Costa County | 828,247.30 | 608,453.40 | 219,793.90 |
| Alameda County | 832,280.70 | 615,394.40 | 216,886.30 |
| Ventura County | 929,082.30 | 713,324.82 | 215,757.48 |
| Fairfield County | 813,346.50 | 625,950.28 | 187,396.22 |
| Montgomery County | 605,150.70 | 433,672.14 | 171,478.56 |
| Salt Lake County | 646,643.80 | 479,462.45 | 167,181.35 |
| Jefferson County | 596,568.00 | 429,564.50 | 167,003.49 |
| Solano County | 531,423.90 | 365,084.04 | 166,339.86 |
| Lee County | 522,553.10 | 358,369.80 | 164,183.30 |
| El Paso County | 629,659.30 | 466,862.03 | 162,797.27 |
| New Haven County | 651,420.90 | 490,126.17 | 161,294.72 |
| Jefferson County/Adams County | 534,445.60 | 383,538.62 | 150,906.98 |
| Douglas County | 476,872.50 | 326,917.69 | 149,954.81 |
| Kane County | 473,797.20 | 330,287.96 | 143,509.25 |
| Wagoner County/Tulsa County | 490,654.40 | 349,631.73 | 141,022.67 |
| Pinellas County | 614,061.70 | 473,579.02 | 140,482.68 |
| Macomb County | 551,135.30 | 412,093.58 | 139,041.71 |
| Nassau County | 526,821.00 | 389,201.90 | 137,619.11 |
| Erie County | 560,836.91 | 427,711.42 | 133,125.49 |
| Santa Clara County | 552,522.20 | 424,582.18 | 127,940.02 |
| Hamilton County | 394,536.20 | 266,941.64 | 127,594.56 |
| Sacramento County | 487,840.40 | 360,658.05 | 127,182.35 |
| Sangamon County | 431,084.70 | 307,719.91 | 123,364.79 |
| Lake County | 381,491.30 | 260,659.55 | 120,831.75 |
| Peoria County | 519,705.61 | 399,689.94 | 120,015.66 |
| San Mateo County | 500,731.20 | 383,800.69 | 116,930.51 |
| Will County/DuPage County | 389,658.60 | 273,109.59 | 116,549.01 |
| Hillsborough County | 463,137.50 | 346,789.62 | 116,347.88 |
| Chatham County/Wake County | 364,995.90 | 255,597.26 | 109,398.64 |
| Johnson County | 492,470.10 | 383,330.35 | 109,139.75 |
| Pueblo County | 380,117.80 | 272,334.80 | 107,783.00 |
| Fresno County | 310,833.10 | 208,058.08 | 102,775.02 |
| Palm Beach County | 366,952.29 | 265,950.91 | 101,001.39 |
| Collin County/Denton County | 469,938.00 | 370,383.87 | 99,554.13 |
| Lane County | 327,268.20 | 227,878.86 | 99,389.34 |
| Vanderburgh County | 342,175.70 | 242,841.53 | 99,334.16 |
| Linn County | 348,768.50 | 249,816.20 | 98,952.30 |
| Richmond County | 345,746.80 | 249,101.41 | 96,645.39 |
| Allen County | 432,290.70 | 337,357.36 | 94,933.33 |
| Forsyth County | 338,758.70 | 247,865.90 | 90,892.80 |
| Queens County | 322,725.60 | 232,418.18 | 90,307.42 |
| Alachua County | 324,112.50 | 235,357.30 | 88,755.20 |
| Spokane County | 326,022.00 | 237,317.52 | 88,704.48 |
| Wayne County | 338,035.10 | 249,589.34 | 88,445.76 |
| Richmond city | 308,193.30 | 219,919.66 | 88,273.64 |
| Essex County | 322,028.80 | 234,104.67 | 87,924.13 |
| San Joaquin County | 278,411.80 | 191,610.72 | 86,801.08 |
| Knox County | 387,735.70 | 300,990.60 | 86,745.10 |
| Mecklenburg County | 322,946.70 | 236,273.99 | 86,672.70 |
| Dane County | 254,499.50 | 167,950.31 | 86,549.19 |
| Washtenaw County | 334,591.30 | 248,200.80 | 86,390.50 |
| Hartford County | 336,058.60 | 250,633.94 | 85,424.67 |
| Cass County | 424,713.00 | 339,483.78 | 85,229.22 |
| Arlington County | 353,029.70 | 270,629.42 | 82,400.28 |
| Martin County/Midland County | 348,400.00 | 266,004.44 | 82,395.56 |
| Bexar County/Comal County/Medina County | 307,074.40 | 225,634.02 | 81,440.38 |
| Denver County | 327,750.60 | 246,324.86 | 81,425.74 |
| Lafayette Parish | 301,747.90 | 221,124.25 | 80,623.65 |
| Clackamas County/Multnomah County/Washington County | 273,989.80 | 194,832.72 | 79,157.08 |
| Doña Ana County | 259,196.20 | 180,272.61 | 78,923.59 |
| Bernalillo County | 262,311.70 | 183,839.36 | 78,472.34 |
| Shelby County/Jefferson County | 273,520.80 | 195,209.09 | 78,311.71 |
| Webb County | 320,286.80 | 242,045.07 | 78,241.73 |
| Summit County | 209,053.40 | 134,084.65 | 74,968.74 |
| Santa Barbara County | 257,032.10 | 182,140.77 | 74,891.33 |
| Passaic County | 305,386.00 | 230,980.36 | 74,405.64 |
| Kaufman County/Dallas County | 243,752.70 | 169,535.26 | 74,217.44 |
| Collin County/Denton County/Dallas County | 268,924.60 | 195,126.41 | 73,798.19 |
| Berkeley County/Charleston County | 325,599.90 | 252,143.55 | 73,456.35 |
| Hampden County | 256,094.10 | 182,760.46 | 73,333.64 |
| Cuyahoga County | 254,050.60 | 180,733.04 | 73,317.56 |
| Monroe County | 341,545.90 | 268,784.80 | 72,761.09 |
| Howard County | 295,905.50 | 225,033.74 | 70,871.76 |
| Lubbock County | 393,129.20 | 322,383.33 | 70,745.87 |
| Tom Green County | 189,167.80 | 118,446.99 | 70,720.81 |
| Aransas County/Kleberg County/Nueces County/San Patricio County | 191,030.40 | 121,247.45 | 69,782.94 |
| McLennan County | 224,785.00 | 155,061.25 | 69,723.75 |
| Warren County/Polk County | 233,937.20 | 165,357.17 | 68,580.03 |
| St. Joseph County | 298,344.30 | 229,938.34 | 68,405.96 |
| Durham County/Orange County/Wake County | 252,543.10 | 184,394.12 | 68,148.98 |
| Davidson County | 224,202.10 | 156,243.13 | 67,958.97 |
| Tarrant County/Ellis County/Dallas County | 232,503.40 | 164,840.03 | 67,663.37 |
| Weld County | 218,453.50 | 150,842.06 | 67,611.44 |
| Milwaukee County/Washington County/Waukesha County | 272,582.80 | 205,268.91 | 67,313.90 |
| Fort Bend County/Harris County/Brazoria County | 251,685.50 | 184,684.29 | 67,001.20 |
| Philadelphia County | 266,378.60 | 199,563.79 | 66,814.81 |
| Adams County/Douglas County/Arapahoe County | 231,860.20 | 165,752.64 | 66,107.56 |
| Sonoma County | 202,165.80 | 136,167.85 | 65,997.95 |
| Chesapeake city | 184,544.80 | 118,818.87 | 65,725.93 |
| Hernando County | 242,935.30 | 177,369.80 | 65,565.50 |
| Newport News city | 204,926.20 | 139,826.59 | 65,099.61 |
| Christian County/Greene County | 224,852.00 | 160,315.05 | 64,536.95 |
| Cumberland County | 237,092.90 | 173,088.67 | 64,004.23 |
| Canadian County/Pottawatomie County/Oklahoma County/Cleveland County | 253,193.00 | 189,286.86 | 63,906.14 |
| District of Columbia | 188,149.40 | 124,393.88 | 63,755.52 |
| Denton County/Dallas County | 269,139.00 | 205,485.48 | 63,653.52 |
| Midland County/Ector County | 291,965.90 | 228,595.89 | 63,370.00 |
| Marion County/Polk County | 286,029.70 | 223,119.65 | 62,910.05 |
| Denton County | 202,299.80 | 140,032.55 | 62,267.25 |
| Adams County/Weld County | 218,359.70 | 156,149.76 | 62,209.93 |
| Harris County/Montgomery County | 226,084.80 | 163,942.94 | 62,141.86 |
| Guilford County | 229,166.80 | 167,407.37 | 61,759.43 |
| Placer County | 197,944.80 | 136,803.55 | 61,141.25 |
| Washington County | 185,154.50 | 124,116.09 | 61,038.40 |
| Rockwall County/Collin County/Dallas County | 178,394.20 | 117,431.44 | 60,962.76 |
| Olmsted County | 286,485.30 | 225,907.18 | 60,578.12 |
| Brevard County | 247,665.50 | 187,187.35 | 60,478.15 |
| Boulder County | 190,226.40 | 129,924.32 | 60,302.08 |
| Baltimore city | 276,549.20 | 216,406.01 | 60,143.18 |
| Wichita County | 290,150.20 | 230,299.74 | 59,850.46 |
| Polk County | 253,032.20 | 193,423.44 | 59,608.76 |
| Shawnee County | 248,998.80 | 190,041.68 | 58,957.12 |
| Lehigh County | 181,663.80 | 123,228.14 | 58,435.66 |
| Allegheny County | 178,025.70 | 119,849.40 | 58,176.30 |
| Bibb County | 274,184.10 | 216,904.16 | 57,279.94 |
| Hudson County | 249,193.10 | 192,758.10 | 56,435.00 |
| Hampton city | 196,343.50 | 139,917.88 | 56,425.63 |
| Alexandria city | 235,230.30 | 178,834.12 | 56,396.18 |
| Kendall County/Kane County/Will County/DuPage County | 222,433.30 | 166,270.95 | 56,162.35 |
| Davidson County/Randolph County/Forsyth County/Guilford County | 208,644.70 | 153,036.68 | 55,608.02 |
| Pierce County | 224,376.30 | 169,123.51 | 55,252.79 |
| Smith County | 228,423.10 | 173,384.88 | 55,038.22 |
| Multnomah County | 169,690.90 | 114,736.63 | 54,954.27 |
| Washoe County | 239,980.60 | 185,136.58 | 54,844.02 |
| Hennepin County | 239,297.20 | 184,547.41 | 54,749.79 |
| Virginia Beach city | 233,950.60 | 179,704.55 | 54,246.04 |
| Collin County/Dallas County | 181,710.70 | 127,669.14 | 54,041.56 |
| Williamson County/Travis County | 149,865.60 | 96,002.09 | 53,863.51 |
| Bronx County | 230,975.80 | 177,172.79 | 53,803.01 |
| Jones County/Taylor County | 164,572.10 | 111,221.24 | 53,350.86 |
| San Francisco County | 202,896.10 | 149,632.04 | 53,264.06 |
| Limestone County/Madison County | 207,840.70 | 155,263.72 | 52,576.98 |
| Yellowstone County | 190,320.20 | 137,820.98 | 52,499.22 |
| Cleveland County | 214,621.10 | 163,183.06 | 51,438.04 |
| Lexington County/Richland County | 202,715.20 | 151,431.09 | 51,284.11 |
| Cook County/DuPage County | 184,799.40 | 133,565.54 | 51,233.86 |
| Cook County/Kane County | 211,746.80 | 160,646.17 | 51,100.63 |
| Ramsey County | 221,341.20 | 170,477.04 | 50,864.15 |
| Clarke County | 197,408.80 | 146,559.65 | 50,849.15 |
| Delaware County/Franklin County/Fairfield County | 189,288.40 | 138,572.68 | 50,715.72 |
| Marion County. | 198,092.20 | 147,674.16 | 50,418.04 |
| Rockwall County/Kaufman County/Collin County/Denton County/Dallas County | 213,328.00 | 162,954.96 | 50,373.05 |
| Snohomish County | 219,210.60 | 169,390.61 | 49,819.99 |
| Lancaster County | 191,492.70 | 141,746.98 | 49,745.72 |
| Orleans Parish | 212,363.20 | 162,727.22 | 49,635.98 |
| Lucas County | 233,454.80 | 184,767.37 | 48,687.43 |
| Ada County | 209,629.60 | 160,966.16 | 48,663.44 |
| Rutherford County | 160,438.20 | 111,855.40 | 48,582.80 |
| Union County | 187,995.30 | 139,822.67 | 48,172.63 |
| Onondaga County | 156,431.60 | 108,436.82 | 47,994.78 |
| Kendall County/Will County | 203,720.20 | 155,980.72 | 47,739.48 |
| Fayette County | 159,051.30 | 111,599.59 | 47,451.71 |
| St. Louis city | 175,660.60 | 128,639.70 | 47,020.90 |
| Fulton County | 169,483.20 | 123,384.92 | 46,098.28 |
| Chatham County | 163,727.90 | 117,651.10 | 46,076.80 |
| Kings County | 186,755.80 | 140,697.29 | 46,058.51 |
| Kent County | 188,430.80 | 142,595.33 | 45,835.47 |
| Utah County | 172,866.70 | 127,101.18 | 45,765.52 |
| Muscogee County | 171,359.20 | 126,028.88 | 45,330.32 |
| St. Lucie County | 191,285.00 | 146,161.04 | 45,123.96 |
| Randall County/Potter County | 286,880.60 | 241,767.36 | 45,113.24 |
| Stanislaus County | 190,769.10 | 146,098.83 | 44,670.27 |
| New York County | 133,041.90 | 88,936.34 | 44,105.56 |
| Winnebago County/Ogle County | 183,526.40 | 139,534.80 | 43,991.60 |
| Wyandotte County | 176,457.90 | 132,601.21 | 43,856.69 |
| Hidalgo County | 151,379.80 | 108,090.26 | 43,289.54 |
| Cass County/Platte County/Jackson County/Clay County | 181,616.90 | 138,911.27 | 42,705.63 |
| Tulare County | 219,967.70 | 177,893.31 | 42,074.39 |
| Dorchester County/Berkeley County/Charleston County | 196,651.70 | 154,724.04 | 41,927.66 |
| Brazos County | 130,911.30 | 89,070.47 | 41,840.83 |
| New Hanover County | 176,511.50 | 134,797.23 | 41,714.27 |
| Winnebago County | 178,112.80 | 136,471.83 | 41,640.97 |
| Tarrant County | 209,810.50 | 168,392.91 | 41,417.59 |
| Bell County | 209,689.90 | 168,272.88 | 41,417.02 |
| Shelby County | 170,287.20 | 128,874.53 | 41,412.67 |
| Arapahoe County | 127,688.60 | 86,646.38 | 41,042.22 |
| Duval County | 138,288.00 | 97,288.72 | 40,999.28 |
| Providence County | 195,298.30 | 154,577.98 | 40,720.32 |
| Sedgwick County | 145,309.60 | 104,670.98 | 40,638.61 |
| Minnehaha County/Lincoln County | 154,006.20 | 113,769.42 | 40,236.78 |
| Hays County/Williamson County/Travis County | 146,086.80 | 106,223.98 | 39,862.82 |
| Jefferson Parish | 232,637.40 | 193,021.31 | 39,616.09 |
| Westchester County | 148,190.60 | 108,579.09 | 39,611.50 |
| East Baton Rouge Parish | 203,177.50 | 163,598.93 | 39,578.57 |
| Pima County | 180,477.90 | 141,320.02 | 39,157.88 |
| Larimer County | 181,985.40 | 143,106.14 | 38,879.26 |
| Mobile County | 109,411.00 | 70,662.32 | 38,748.68 |
| Rogers County/Wagoner County/Osage County/Tulsa County | 206,741.90 | 168,177.91 | 38,563.99 |
| DeKalb County/Fulton County | 166,133.20 | 127,989.46 | 38,143.74 |
| Brown County | 133,336.70 | 96,200.24 | 37,136.46 |
| Scott County | 217,354.70 | 180,285.88 | 37,068.82 |
| Cameron County | 159,707.90 | 122,726.35 | 36,981.55 |
| Fort Bend County/Harris County/Montgomery County | 218,212.30 | 181,980.44 | 36,231.86 |
| Richmond County/Queens County/Kings County/New York County/Bronx County | 118,087.50 | 82,143.14 | 35,944.36 |
| Rockland County | 277,420.20 | 241,512.52 | 35,907.68 |
| Harris County | 151,098.40 | 115,329.34 | 35,769.05 |
| Leon County | 126,569.70 | 91,962.22 | 34,607.48 |
| Honolulu County | 158,247.30 | 123,904.71 | 34,342.59 |
| Rankin County/Hinds County/Madison County | 178,179.80 | 144,369.59 | 33,810.21 |
| Clinton County/Ingham County/Eaton County | 125,068.90 | 91,956.16 | 33,112.74 |
| Collin County | 119,769.20 | 87,694.02 | 32,075.18 |
| Norfolk city | 146,140.40 | 114,087.33 | 32,053.07 |
| Dallas County | 165,255.50 | 133,452.01 | 31,803.49 |
| Caddo Parish/Bossier Parish | 134,723.60 | 103,177.22 | 31,546.38 |
| Worcester County | 150,187.20 | 118,962.22 | 31,224.98 |
| Jackson County/Clay County | 117,377.30 | 87,033.30 | 30,344.00 |
| Boone County | 159,439.90 | 130,614.79 | 28,825.11 |
| Pulaski County | 114,630.30 | 86,968.18 | 27,662.12 |
| Kern County | 159,875.40 | 135,366.67 | 24,508.73 |
| Parker County/Johnson County/Tarrant County/Wise County/Denton County | 129,665.10 | 105,541.32 | 24,123.79 |
| Durham County/Wake County | 96,754.70 | 76,775.97 | 19,978.73 |
| Monterey County | 97,444.80 | 78,920.10 | 18,524.70 |
| Maricopa County/Yavapai County | 76,768.60 | 60,517.32 | 16,251.29 |
SELECT s.city_name AS City,
round(SUM(rt.sales), 2) AS Total_Sales,
round(SUM(rt.cost), 2) AS Total_Cost,
round(SUM(rt.profit), 2) AS Total_Profit
FROM revenue_tbl rt
JOIN store s
ON rt.store_id = s.store_id
GROUP BY s.city_name
ORDER BY Total_Cost DESC;| City | Total Sales | Total Cost | Total Profit |
|---|---|---|---|
| Columbia | 658,060.60 | 507,079.62 | 150,980.98 |
| Rochester | 628,031.20 | 494,691.99 | 133,339.21 |
| Springfield | 660,017.00 | 484,520.62 | 175,496.38 |
| Arlington | 562,840.20 | 439,022.32 | 123,817.87 |
| Warren | 528,147.60 | 416,131.71 | 112,015.89 |
| Richmond | 571,744.50 | 408,270.43 | 163,474.07 |
| Wayne | 502,654.10 | 367,150.25 | 135,503.85 |
| Broken Arrow | 490,654.40 | 349,631.73 | 141,022.67 |
| Fargo | 424,713.00 | 339,483.78 | 85,229.22 |
| Aurora | 454,293.50 | 332,023.59 | 122,269.91 |
| Costa Mesa | 443,841.50 | 328,654.36 | 115,187.13 |
| Lubbock | 393,129.20 | 322,383.33 | 70,745.87 |
| Pasadena | 408,525.80 | 314,907.54 | 93,618.26 |
| Knoxville | 387,735.70 | 300,990.60 | 86,745.10 |
| Escondido | 389,605.00 | 289,039.54 | 100,565.46 |
| West Jordan | 387,802.70 | 281,454.64 | 106,348.06 |
| Colorado Springs | 348,527.30 | 275,553.51 | 72,973.78 |
| El Cajon | 362,483.40 | 275,332.92 | 87,150.48 |
| Naperville | 389,658.60 | 273,109.59 | 116,549.01 |
| Pueblo | 380,117.80 | 272,334.80 | 107,783.00 |
| Kansas City | 358,074.80 | 271,512.48 | 86,562.32 |
| St. Petersburg | 345,271.10 | 268,650.30 | 76,620.79 |
| Carlsbad | 350,617.70 | 266,104.17 | 84,513.53 |
| Midland | 348,400.00 | 266,004.44 | 82,395.56 |
| West Palm Beach | 366,952.29 | 265,950.91 | 101,001.39 |
| Columbus | 360,647.60 | 264,601.56 | 96,046.04 |
| Torrance | 336,587.90 | 263,138.68 | 73,449.22 |
| Glendale | 357,431.60 | 261,650.14 | 95,781.45 |
| Vista | 336,514.20 | 260,549.37 | 75,964.83 |
| Peoria | 343,951.20 | 260,397.11 | 83,554.09 |
| Washington | 368,560.30 | 258,411.23 | 110,149.07 |
| Los Angeles | 315,261.80 | 258,132.14 | 57,129.66 |
| Orlando | 314,993.80 | 257,412.16 | 57,581.64 |
| Cary | 364,995.90 | 255,597.26 | 109,398.64 |
| Charleston | 325,599.90 | 252,143.55 | 73,456.35 |
| Center | 354,510.40 | 251,244.27 | 103,266.13 |
| Cedar Rapids | 348,768.50 | 249,816.20 | 98,952.30 |
| Detroit | 338,035.10 | 249,589.34 | 88,445.76 |
| Ann Arbor | 334,591.30 | 248,200.80 | 86,390.50 |
| Winston-Salem | 338,758.70 | 247,865.90 | 90,892.80 |
| Bloomingdale (Township) | 312,695.70 | 247,043.00 | 65,652.69 |
| Denver | 327,750.60 | 246,324.86 | 81,425.74 |
| Santa Clarita | 307,724.30 | 245,372.76 | 62,351.54 |
| Evansville | 342,175.70 | 242,841.53 | 99,334.16 |
| Laredo | 320,286.80 | 242,045.07 | 78,241.73 |
| Amarillo | 286,880.60 | 241,767.36 | 45,113.24 |
| Ramapo | 277,420.20 | 241,512.52 | 35,907.68 |
| Scottsdale | 282,867.30 | 238,654.44 | 44,212.86 |
| Temecula | 315,067.50 | 237,341.97 | 77,725.53 |
| Spokane | 326,022.00 | 237,317.52 | 88,704.48 |
| Charlotte | 322,946.70 | 236,273.99 | 86,672.70 |
| Gainesville | 324,112.50 | 235,357.30 | 88,755.20 |
| Newark | 322,028.80 | 234,104.67 | 87,924.13 |
| Stamford (Town) | 278,713.30 | 233,375.67 | 45,337.63 |
| Vallejo | 351,783.50 | 233,029.52 | 118,753.98 |
| Queens | 322,725.60 | 232,418.18 | 90,307.42 |
| Antioch | 318,631.90 | 231,562.25 | 87,069.65 |
| Paterson | 305,386.00 | 230,980.36 | 74,405.64 |
| Wichita Falls | 290,150.20 | 230,299.74 | 59,850.46 |
| South Bend | 298,344.30 | 229,938.34 | 68,405.96 |
| Odessa | 291,965.90 | 228,595.89 | 63,370.00 |
| Eugene | 327,268.20 | 227,878.86 | 99,389.34 |
| San Antonio | 307,074.40 | 225,634.02 | 81,440.38 |
| Lehigh Acres | 324,105.80 | 223,858.73 | 100,247.08 |
| Salem | 286,029.70 | 223,119.65 | 62,910.05 |
| Amherst | 288,173.70 | 223,009.00 | 65,164.70 |
| Lancaster | 238,948.80 | 221,363.01 | 17,585.79 |
| Lafayette | 301,747.90 | 221,124.25 | 80,623.65 |
| Simi Valley | 267,738.70 | 220,686.78 | 47,051.92 |
| Sacramento | 292,582.30 | 220,214.29 | 72,368.01 |
| Miramar | 293,473.40 | 219,759.70 | 73,713.70 |
| Woodbridge (Township) | 311,710.80 | 218,924.08 | 92,786.73 |
| Hollywood | 359,347.80 | 218,800.93 | 140,546.87 |
| Palmdale | 276,649.70 | 217,616.34 | 59,033.36 |
| Macon | 274,184.10 | 216,904.16 | 57,279.94 |
| Frisco | 275,316.40 | 216,753.95 | 58,562.46 |
| Baltimore | 276,549.20 | 216,406.01 | 60,143.18 |
| Clarksville | 308,441.20 | 213,937.97 | 94,503.23 |
| Victorville | 263,122.40 | 210,994.66 | 52,127.74 |
| Chula Vista | 281,433.50 | 210,226.67 | 71,206.83 |
| Sunrise Manor | 284,482.00 | 209,909.16 | 74,572.84 |
| Pompano Beach | 268,562.80 | 209,821.96 | 58,740.84 |
| San Mateo | 279,396.70 | 208,766.41 | 70,630.29 |
| Olathe | 273,721.80 | 208,509.93 | 65,211.87 |
| Fremont | 271,470.60 | 207,820.23 | 63,650.37 |
| Sunnyvale | 269,688.40 | 207,720.70 | 61,967.70 |
| Pembroke Pines | 252,737.40 | 206,972.21 | 45,765.18 |
| Pomona | 262,184.40 | 206,869.97 | 55,314.43 |
| Lewisville | 269,139.00 | 205,485.48 | 63,653.52 |
| Milwaukee | 272,582.80 | 205,268.91 | 67,313.90 |
| Clearwater | 268,790.60 | 204,928.71 | 63,861.89 |
| Buffalo | 272,663.20 | 204,702.42 | 67,960.78 |
| Oceanside | 272,522.50 | 201,354.93 | 71,167.57 |
| Downey | 272,743.60 | 200,538.94 | 72,204.66 |
| Peoria City | 252,523.00 | 199,810.15 | 52,712.86 |
| Philadelphia | 266,378.60 | 199,563.79 | 66,814.81 |
| Cambridge | 283,564.10 | 199,236.63 | 84,327.47 |
| Westminster | 289,922.40 | 197,386.42 | 92,535.98 |
| Phoenix | 270,164.10 | 197,142.04 | 73,022.06 |
| Birmingham | 273,520.80 | 195,209.09 | 78,311.71 |
| Carrollton | 268,924.60 | 195,126.41 | 73,798.19 |
| Henderson | 275,122.10 | 194,884.98 | 80,237.12 |
| Portland | 273,989.80 | 194,832.72 | 79,157.08 |
| Miami Gardens | 273,018.30 | 194,203.52 | 78,814.78 |
| Lakeland | 253,032.20 | 193,423.44 | 59,608.76 |
| Metairie | 232,637.40 | 193,021.31 | 39,616.09 |
| Miami | 264,087.20 | 192,764.63 | 71,322.57 |
| Jersey City | 249,193.10 | 192,758.10 | 56,435.00 |
| Lowell | 239,022.50 | 192,179.92 | 46,842.58 |
| Fort Lauderdale | 269,145.70 | 192,020.29 | 77,125.41 |
| Milton | 238,164.90 | 191,980.33 | 46,184.58 |
| Hialeah | 278,766.90 | 191,937.88 | 86,829.02 |
| Stockton | 278,411.80 | 191,610.72 | 86,801.08 |
| El Paso | 281,132.00 | 191,308.52 | 89,823.48 |
| Lawrence (Township) | 266,861.00 | 190,991.61 | 75,869.39 |
| Huntington Beach | 268,402.00 | 190,565.76 | 77,836.24 |
| Topeka | 248,998.80 | 190,041.68 | 58,957.12 |
| Kent | 248,549.90 | 189,347.13 | 59,202.77 |
| Oklahoma City | 253,193.00 | 189,286.86 | 63,906.14 |
| Concord | 246,064.20 | 188,540.38 | 57,523.82 |
| Vancouver | 280,703.20 | 188,507.85 | 92,195.35 |
| Riverside | 274,988.10 | 187,298.00 | 87,690.10 |
| Palm Bay | 247,665.50 | 187,187.35 | 60,478.15 |
| Thousand Oaks | 249,380.70 | 186,501.17 | 62,879.53 |
| Arvada | 244,523.20 | 186,152.20 | 58,371.00 |
| Highlands Ranch | 274,914.40 | 186,092.84 | 88,821.56 |
| Reno | 239,980.60 | 185,136.58 | 54,844.02 |
| Aurora (Township) | 257,032.10 | 184,769.79 | 72,262.31 |
| Toledo | 233,454.80 | 184,767.37 | 48,687.43 |
| Pearland | 251,685.50 | 184,684.29 | 67,001.20 |
| Minneapolis | 239,297.20 | 184,547.41 | 54,749.79 |
| Durham | 252,543.10 | 184,394.12 | 68,148.98 |
| Albuquerque | 262,311.70 | 183,839.36 | 78,472.34 |
| Bridgeport | 242,660.60 | 183,424.16 | 59,236.44 |
| Santa Maria | 257,032.10 | 182,140.77 | 74,891.33 |
| Houston | 218,212.30 | 181,980.44 | 36,231.86 |
| Lisle (Township) | 233,280.60 | 181,883.86 | 51,396.74 |
| Hayward | 255,698.80 | 181,685.41 | 74,013.39 |
| Cleveland | 254,050.60 | 180,733.04 | 73,317.56 |
| Schaumburg (Township) | 221,569.00 | 180,498.24 | 41,070.77 |
| Spring Valley | 252,094.20 | 180,426.78 | 71,667.42 |
| Davenport | 217,354.70 | 180,285.88 | 37,068.82 |
| Las Cruces | 259,196.20 | 180,272.61 | 78,923.59 |
| Murrieta | 243,632.10 | 179,850.31 | 63,781.79 |
| Virginia Beach | 233,950.60 | 179,704.55 | 54,246.04 |
| Alexandria | 235,230.30 | 178,834.12 | 56,396.18 |
| Naperville (Township) | 243,411.00 | 178,538.08 | 64,872.92 |
| Renton | 240,784.60 | 178,171.36 | 62,613.24 |
| Visalia | 219,967.70 | 177,893.31 | 42,074.39 |
| Spring Hill | 242,935.30 | 177,369.80 | 65,565.50 |
| The Bronx | 230,975.80 | 177,172.79 | 53,803.01 |
| Boston | 226,594.00 | 176,533.58 | 50,060.42 |
| Las Vegas | 242,372.50 | 176,253.78 | 66,118.71 |
| Niles (Township) | 221,883.90 | 176,065.28 | 45,818.62 |
| San Buenaventura (Ventura) | 233,629.00 | 175,863.07 | 57,765.92 |
| Daly City | 221,334.50 | 175,034.28 | 46,300.22 |
| Overland Park | 218,748.30 | 174,820.42 | 43,927.88 |
| Oyster Bay | 227,170.20 | 174,617.44 | 52,552.76 |
| Brookhaven | 241,863.30 | 174,205.13 | 67,658.17 |
| Ontario | 226,768.20 | 173,839.10 | 52,929.09 |
| Tyler | 228,423.10 | 173,384.88 | 55,038.22 |
| Fayetteville | 237,092.90 | 173,088.67 | 64,004.23 |
| Jurupa Valley | 241,896.80 | 172,887.57 | 69,009.23 |
| Manchester | 224,704.60 | 172,854.74 | 51,849.86 |
| Tempe | 227,538.70 | 170,782.60 | 56,756.10 |
| St. Paul | 221,341.20 | 170,477.04 | 50,864.15 |
| Irvine | 208,329.80 | 169,748.29 | 38,581.51 |
| Mesquite | 243,752.70 | 169,535.26 | 74,217.44 |
| Everett | 219,210.60 | 169,390.61 | 49,819.99 |
| Tacoma | 224,376.30 | 169,123.51 | 55,252.79 |
| El Monte | 239,913.60 | 168,880.54 | 71,033.06 |
| Killeen | 209,689.90 | 168,272.88 | 41,417.02 |
| Tulsa | 206,741.90 | 168,177.91 | 38,563.99 |
| Madison | 254,499.50 | 167,950.31 | 86,549.19 |
| Orange | 232,382.80 | 167,436.28 | 64,946.52 |
| Greensboro | 229,166.80 | 167,407.37 | 61,759.43 |
| Anaheim | 215,063.30 | 166,318.72 | 48,744.58 |
| Capital | 252,013.80 | 166,274.81 | 85,739.00 |
| Lakewood | 230,721.20 | 165,662.66 | 65,058.54 |
| East Los Angeles | 221,964.30 | 165,637.13 | 56,327.17 |
| Des Moines | 233,937.20 | 165,357.17 | 68,580.03 |
| Fontana | 225,709.60 | 164,905.39 | 60,804.21 |
| Grand Prairie | 232,503.40 | 164,840.03 | 67,663.37 |
| The Woodlands | 226,084.80 | 163,942.94 | 62,141.86 |
| Berkeley | 222,962.60 | 163,647.87 | 59,314.73 |
| Baton Rouge | 203,177.50 | 163,598.93 | 39,578.57 |
| Norman | 214,621.10 | 163,183.06 | 51,438.04 |
| Dallas | 213,328.00 | 162,954.96 | 50,373.05 |
| New Orleans | 212,363.20 | 162,727.22 | 49,635.98 |
| Edison | 212,825.50 | 161,698.34 | 51,127.16 |
| Louisville | 223,016.20 | 161,566.78 | 61,449.42 |
| Boise City | 209,629.60 | 160,966.16 | 48,663.44 |
| Palatine (Township) | 215,927.60 | 160,773.23 | 55,154.37 |
| Elgin | 211,746.80 | 160,646.17 | 51,100.63 |
| Burbank | 191,090.70 | 159,085.34 | 32,005.36 |
| Bridgeport (Town) | 216,343.00 | 159,034.65 | 57,308.35 |
| Proviso | 205,321.50 | 157,476.40 | 47,845.10 |
| Corona | 198,454.00 | 157,377.71 | 41,076.29 |
| Wheeling (Township) | 211,894.20 | 156,597.86 | 55,296.34 |
| Nashville | 224,202.10 | 156,243.13 | 67,958.97 |
| Thornton | 218,359.70 | 156,149.76 | 62,209.93 |
| Joliet | 203,720.20 | 155,980.72 | 47,739.48 |
| New Haven | 221,301.00 | 155,725.82 | 65,575.18 |
| Huntsville | 207,840.70 | 155,263.72 | 52,576.98 |
| Sterling Heights | 232,711.10 | 155,136.26 | 77,574.84 |
| Waco | 224,785.00 | 155,061.25 | 69,723.75 |
| North Charleston | 196,651.70 | 154,724.04 | 41,927.66 |
| Providence | 195,298.30 | 154,577.98 | 40,720.32 |
| Cincinnati | 226,942.40 | 154,534.09 | 72,408.30 |
| Plano | 194,621.60 | 153,629.93 | 40,991.67 |
| High Point | 208,644.70 | 153,036.68 | 55,608.02 |
| Waterbury | 194,246.40 | 151,485.93 | 42,760.47 |
| Greeley | 218,453.50 | 150,842.06 | 67,611.44 |
| Fullerton | 217,401.60 | 149,996.32 | 67,405.28 |
| York | 225,501.90 | 149,978.63 | 75,523.27 |
| Bremen | 210,514.00 | 149,772.64 | 60,741.36 |
| Islip | 187,807.70 | 149,667.88 | 38,139.82 |
| San Francisco | 202,896.10 | 149,632.04 | 53,264.06 |
| Long Beach | 212,979.60 | 149,588.39 | 63,391.21 |
| San Bernardino | 179,426.00 | 148,898.02 | 30,527.98 |
| Indianapolis | 198,092.20 | 147,674.16 | 50,418.04 |
| Athens | 197,408.80 | 146,559.65 | 50,849.15 |
| Port St. Lucie | 191,285.00 | 146,161.04 | 45,123.96 |
| Rancho Cucamonga | 195,827.60 | 146,110.32 | 49,717.28 |
| Modesto | 190,769.10 | 146,098.83 | 44,670.27 |
| Elgin (Township) | 216,765.10 | 145,518.17 | 71,246.93 |
| Jackson | 178,179.80 | 144,369.59 | 33,810.21 |
| San Diego | 224,262.40 | 144,199.64 | 80,062.76 |
| Fort Collins | 181,985.40 | 143,106.14 | 38,879.26 |
| Grand Rapids | 188,430.80 | 142,595.33 | 45,835.47 |
| Santa Ana | 210,500.60 | 141,890.99 | 68,609.61 |
| Lincoln | 191,492.70 | 141,746.98 | 49,745.72 |
| Augusta | 190,836.10 | 141,389.53 | 49,446.57 |
| Tucson | 180,477.90 | 141,320.02 | 39,157.88 |
| Omaha | 201,958.10 | 140,824.85 | 61,133.25 |
| Fort Wayne | 166,327.50 | 140,779.40 | 25,548.10 |
| Brooklyn | 186,755.80 | 140,697.29 | 46,058.51 |
| Elk Grove | 195,258.10 | 140,443.76 | 54,814.34 |
| Denton | 202,299.80 | 140,032.55 | 62,267.25 |
| Hampton | 196,343.50 | 139,917.88 | 56,425.63 |
| Newport News | 204,926.20 | 139,826.59 | 65,099.61 |
| Elizabeth | 187,995.30 | 139,822.67 | 48,172.63 |
| Rockford | 183,526.40 | 139,534.80 | 43,991.60 |
| Hartford (Town) | 182,327.10 | 138,394.63 | 43,932.47 |
| Garden Grove | 180,685.60 | 138,249.84 | 42,435.76 |
| Billings | 190,320.20 | 137,820.98 | 52,499.22 |
| Seattle | 175,050.90 | 136,873.30 | 38,177.60 |
| Roseville | 197,944.80 | 136,803.55 | 61,141.25 |
| Rockford (Township) | 178,112.80 | 136,471.83 | 41,640.97 |
| Santa Rosa | 202,165.80 | 136,167.85 | 65,997.95 |
| Perry | 186,876.40 | 135,745.12 | 51,131.28 |
| Bakersfield | 159,875.40 | 135,366.67 | 24,508.73 |
| Wilmington | 176,511.50 | 134,797.23 | 41,714.27 |
| Cape Coral | 198,447.30 | 134,511.08 | 63,936.22 |
| Gilbert | 189,998.60 | 134,327.63 | 55,670.97 |
| Akron | 209,053.40 | 134,084.65 | 74,968.74 |
| Santa Clara | 170,340.80 | 134,071.66 | 36,269.14 |
| Chicago | 184,799.40 | 133,565.54 | 51,233.86 |
| Irving | 165,255.50 | 133,452.01 | 31,803.49 |
| Coral Springs | 178,508.10 | 133,413.32 | 45,094.79 |
| Lyons (Township) | 200,115.60 | 132,937.92 | 67,177.69 |
| Fairfield | 179,640.40 | 132,054.52 | 47,585.88 |
| Chandler | 175,600.30 | 131,894.56 | 43,705.74 |
| Calumet | 188,377.20 | 130,954.48 | 57,422.72 |
| Oxnard | 178,333.90 | 130,273.80 | 48,060.11 |
| Maine | 168,786.40 | 130,092.29 | 38,694.11 |
| Hanover | 175,808.00 | 130,018.83 | 45,789.17 |
| Boulder | 190,226.40 | 129,924.32 | 60,302.08 |
| North | 193,114.10 | 129,705.07 | 63,409.03 |
| Downers Grove (Township) | 177,469.60 | 129,364.44 | 48,105.16 |
| Memphis | 170,287.20 | 128,874.53 | 41,412.67 |
| St. Louis | 175,660.60 | 128,639.70 | 47,020.90 |
| Atlanta | 166,133.20 | 127,989.46 | 38,143.74 |
| Richardson | 181,710.70 | 127,669.14 | 54,041.56 |
| Provo | 172,866.70 | 127,101.18 | 45,765.52 |
| Hillsboro | 185,154.50 | 124,116.09 | 61,038.40 |
| Honolulu | 158,247.30 | 123,904.71 | 34,342.59 |
| Salt Lake City | 153,677.90 | 123,504.75 | 30,173.15 |
| Sandy Springs | 169,483.20 | 123,384.92 | 46,098.28 |
| Allentown | 181,663.80 | 123,228.14 | 58,435.66 |
| Brownsville | 159,707.90 | 122,726.35 | 36,981.55 |
| Corpus Christi | 191,030.40 | 121,247.45 | 69,782.94 |
| Bellevue | 184,873.10 | 120,932.89 | 63,940.21 |
| Pittsburgh | 178,025.70 | 119,849.40 | 58,176.30 |
| Montgomery | 154,863.80 | 119,418.82 | 35,444.98 |
| Worcester | 150,187.20 | 118,962.22 | 31,224.98 |
| Chesapeake | 184,544.80 | 118,818.87 | 65,725.93 |
| San Angelo | 189,167.80 | 118,446.99 | 70,720.81 |
| Savannah | 163,727.90 | 117,651.10 | 46,076.80 |
| Garland | 178,394.20 | 117,431.44 | 60,962.76 |
| Waterbury (Town) | 152,358.00 | 117,228.49 | 35,129.51 |
| North Las Vegas | 157,791.70 | 116,881.23 | 40,910.47 |
| Gresham | 169,690.90 | 114,736.63 | 54,954.27 |
| Mesa | 156,203.80 | 114,335.50 | 41,868.30 |
| Babylon (Town) | 167,942.20 | 114,164.52 | 53,777.68 |
| Norfolk | 146,140.40 | 114,087.33 | 32,053.07 |
| Sioux Falls | 154,006.20 | 113,769.42 | 40,236.78 |
| Chattanooga | 167,593.80 | 112,407.54 | 55,186.26 |
| Hartford | 153,731.50 | 112,239.31 | 41,492.20 |
| Huntington | 149,121.90 | 112,071.27 | 37,050.63 |
| Murfreesboro | 160,438.20 | 111,855.40 | 48,582.80 |
| Lexington-Fayette | 159,051.30 | 111,599.59 | 47,451.71 |
| Abilene | 164,572.10 | 111,221.24 | 53,350.86 |
| Enterprise | 153,041.40 | 111,170.15 | 41,871.25 |
| Thornton (Township) | 167,969.00 | 109,576.56 | 58,392.44 |
| Fresno | 161,161.80 | 109,308.16 | 51,853.64 |
| Hempstead (Town) | 161,047.90 | 108,929.67 | 52,118.23 |
| Yonkers | 148,190.60 | 108,579.09 | 39,611.50 |
| Syracuse | 156,431.60 | 108,436.82 | 47,994.78 |
| McAllen | 151,379.80 | 108,090.26 | 43,289.54 |
| Staten Island | 154,910.70 | 107,711.88 | 47,198.82 |
| Austin | 146,086.80 | 106,223.98 | 39,862.82 |
| North Hempstead | 138,602.90 | 105,654.78 | 32,948.12 |
| Fort Worth | 129,665.10 | 105,541.32 | 24,123.79 |
| Wichita | 145,309.60 | 104,670.98 | 40,638.61 |
| Shreveport | 134,723.60 | 103,177.22 | 31,546.38 |
| Norwalk | 135,842.50 | 102,655.22 | 33,187.28 |
| Beaumont | 142,830.60 | 102,335.06 | 40,495.54 |
| Brandon | 135,172.50 | 101,457.53 | 33,714.97 |
| Dayton | 141,845.70 | 100,315.35 | 41,530.35 |
| Rialto | 152,384.80 | 99,051.53 | 53,333.27 |
| Clovis | 149,671.30 | 98,749.93 | 50,921.37 |
| Jacksonville | 138,288.00 | 97,288.72 | 40,999.28 |
| Green Bay | 133,336.70 | 96,200.24 | 37,136.46 |
| Round Rock | 149,865.60 | 96,002.09 | 53,863.51 |
| Tallahassee | 126,569.70 | 91,962.22 | 34,607.48 |
| Lansing | 125,068.90 | 91,956.16 | 33,112.74 |
| Smithtown | 130,509.30 | 90,853.91 | 39,655.39 |
| West Covina | 126,308.40 | 90,600.15 | 35,708.25 |
| Inglewood | 122,643.50 | 89,582.15 | 33,061.35 |
| College Station | 130,911.30 | 89,070.47 | 41,840.83 |
| Manhattan | 133,041.90 | 88,936.34 | 44,105.56 |
| McKinney | 119,769.20 | 87,694.02 | 32,075.18 |
| Independence | 117,377.30 | 87,033.30 | 30,344.00 |
| Little Rock | 114,630.30 | 86,968.18 | 27,662.12 |
| Worth (Township) | 114,543.20 | 86,892.20 | 27,651.00 |
| Centennial | 127,688.60 | 86,646.38 | 41,042.22 |
| San Jose | 112,493.00 | 82,789.82 | 29,703.18 |
| New York City | 118,087.50 | 82,143.14 | 35,944.36 |
| Paradise | 91,441.60 | 81,614.38 | 9,827.23 |
| Salinas | 97,444.80 | 78,920.10 | 18,524.70 |
| Raleigh | 96,754.70 | 76,775.97 | 19,978.73 |
| West Valley City | 105,163.20 | 74,503.06 | 30,660.14 |
| Tampa | 103,260.40 | 72,477.35 | 30,783.05 |
| Surprise | 101,739.50 | 71,197.85 | 30,541.65 |
| Mobile | 109,411.00 | 70,662.32 | 38,748.68 |
| New Haven (Town) | 83,515.50 | 65,685.93 | 17,829.57 |
| Oakland | 82,148.70 | 62,240.89 | 19,907.81 |
| Moreno Valley | 87,126.80 | 61,443.49 | 25,683.31 |
| Stamford | 75,629.60 | 50,115.80 | 25,513.80 |
| Davie | 69,083.70 | 49,528.31 | 19,555.39 |
Top 5 locations with the highest profit:
SELECT r.region, s.state, s.county, s.city_name,
round(SUM(rt.sales), 2) AS Total_Sales,
round(SUM(rt.cost), 2) AS Total_Cost,
round(SUM(rt.profit), 2) AS Total_Profit
FROM revenue_tbl rt
JOIN store s
ON rt.store_id = s.store_id
JOIN regions r
ON s.state_code = r.state_code
GROUP BY r.region, s.state, s.county, s.city_name
ORDER BY Total_Profit DESC
LIMIT 5;| Location | Total Sales | Total Cost | Total Profit |
|---|---|---|---|
| South
Region, Oklahoma State, Wagoner County/Tulsa County Broken Arrow |
490,654.40 | 349,631.73 | 141,022.67 |
| South
Region, Florida State, Broward County Hollywood |
359,347.80 | 218,800.93 | 140,546.87 |
| West
Region, California State, Solano County Vallejo |
351,783.50 | 233,029.52 | 118,753.98 |
| Midwest
Region, Illinois State, Will County/DuPage County Naperville |
389,658.60 | 273,109.59 | 116,549.01 |
| West
Region, California State, Orange County Costa Mesa |
443,841.50 | 328,654.36 | 115,187.13 |
SELECT r.region, s.state, s.county, s.city_name,
round(SUM(rt.sales), 2) AS Total_Sales,
round(SUM(rt.cost), 2) AS Total_Cost,
round(SUM(rt.profit), 2) AS Total_Profit
FROM revenue_tbl rt
JOIN store s
ON rt.store_id = s.store_id
JOIN regions r
ON s.state_code = r.state_code
WHERE order_year = 2020
GROUP BY r.region, s.state, s.county, s.city_name
ORDER BY Total_Profit DESC
LIMIT 5;| Location | Total Sales | Total Cost | Total Profit |
|---|---|---|---|
| West
Region, California State, Solano County Vallejo |
199,579.60 | 127,302.68 | 72,276.92 |
| South
Region, Florida State, Broward County Hollywood |
165,878.60 | 100,464.09 | 65,414.51 |
| Northeast
Region, Massachusetts State, Middlesex County Cambridge |
207,097.00 | 142,282.94 | 64,814.06 |
| West
Region, California State, Riverside County Riverside |
191,104.10 | 127,071.66 | 64,032.43 |
| West
Region, California State, Orange County Costa Mesa |
228,302.50 | 165,363.81 | 62,938.69 |
Insight
In 2020 The store at Vallejo in Solano County in the state of California within the West region made the most profit for the company. While for all the past years including 2020 Broken Arrow store at Wagoner County in Oklahoma within the Southern region of the country made the most profit.