# Who are our biggest vendors by spend?

> A real Claude session on a Fishbowl database (ILC's demo company, sample data), connected through ILC MCP for Fishbowl. The answer below is what the AI said; the queries are what it ran. Page: https://israellopezconsulting.com/fishbowl-mcp/questions/vendor-spend

## Question

Who are our biggest vendors by spend?

### Answer

Chuck's Flour Shop is the biggest vendor at $1,775, about 30% of the $6,003.63 in purchase order spend in the DEMO Fishbowl database. The top three vendors account for roughly two-thirds of the total.

| Rank | Vendor | POs | Spend | Share |
|---|---|---|---|---|
| 1 | Chuck's Flour Shop | 3 | $1,775.00 | 29.6% |
| 2 | Johnson Manufacturing | 5 | $1,097.52 | 18.3% |
| 3 | A&B Distribution | 4 | $1,067.30 | 17.8% |
| 4 | Williams Treat Company | 4 | $863.20 | 14.4% |
| 5 | Brent's Food Supply | 3 | $519.00 | 8.6% |
| 6 | Rocky Mountain Chocolate | 5 | $491.46 | 8.2% |
| 7 | Mike's Salt | 4 | $148.90 | 2.5% |
| 8 | One Stop Office Paper Shop | 2 | $41.25 | 0.7% |

- **What counts as spend:** PO line totals on issued, fulfilled and closed-short POs. The six bid requests are excluded, and this is not vendor bills or payments from accounting.
- **Period:** every PO in the database was issued between July 10 and August 14, 2026, so this is all-time and only about five weeks of activity.
- **Ordered vs. received:** received value matches ordered value to within cents for every vendor, so ranking by receipts gives the same order.
- **Zero-dollar vendors:** Jill's Energy Source (2 POs) and Utah State Tax Commission (1 PO) have POs that total $0.

## Behind the scenes

Every step the AI took, as recorded in the query log. All queries are read-only and row-capped.

1. Checked the saved queries — 6 saved queries
2. Read the Fishbowl notes index — 13 topic notes
3. Looked up a table (po) — 51 columns
4. Looked up a table (poitem) — 31 columns
5. Ran a read-only query: Understand PO statuses and date range before ranking vendors by purchase spend — 4 rows, 231 ms

   ```sql
   SELECT s.id, s.name, COUNT(*) AS poCount, MIN(po.dateIssued) AS firstIssued, MAX(po.dateIssued) AS lastIssued
   FROM po
   JOIN postatus AS s ON s.id = po.statusId
   GROUP BY s.id, s.name
   ORDER BY s.id
   LIMIT 101
   ```

6. Ran a read-only query: Rank vendors by total purchase order spend, excluding bid requests — 10 rows, 208 ms

   ```sql
   SELECT v.name AS vendor, COUNT(DISTINCT po.id) AS poCount, ROUND(SUM(CASE WHEN pit.name LIKE '%Credit%' THEN -1 ELSE 1 END * pi.totalCost), 2) AS orderedSpend, ROUND(SUM(CASE WHEN pit.name LIKE '%Credit%' THEN -1 ELSE 1 END * pi.unitCost * COALESCE(pi.qtyFulfilled, 0)), 2) AS receivedSpend, SUM(pit.name LIKE '%Credit%') AS creditLines, COUNT(DISTINCT po.currencyId) AS currencies, MIN(DATE(po.dateIssued)) AS firstPO, MAX(DATE(po.dateIssued)) AS lastPO
   FROM po
   JOIN vendor AS v ON v.id = po.vendorId
   JOIN poitem AS pi ON pi.poId = po.id
   JOIN poitemtype AS pit ON pit.id = pi.typeId
   WHERE po.statusId IN (20, 30, 40, 50, 55, 60, 70)
   GROUP BY v.name
   ORDER BY orderedSpend DESC
   LIMIT 101
   ```

Learn more: https://israellopezconsulting.com/fishbowl-mcp · More examples: https://israellopezconsulting.com/fishbowl-mcp/questions