Query · SQL
Revenue by state and salesperson
Posted-sales revenue grouped by ship-to state and the assigned salesperson — a fast territory and commission view.
What people ask
- What is our revenue by state and salesperson?”
- How much revenue has each salesperson generated in each state?”
- Total sales by region and salesperson”
The query
revenue-by-state-salesperson.sql
SELECT COALESCE(sc.code,'Unknown') AS state, COALESCE(u.userName,'Unassigned') AS salesperson, ROUND(SUM(psi.totalPrice),2) AS revenue FROM postso ps JOIN postsoitem psi ON psi.postSOId = ps.id JOIN postsoitem si ON si.id = psi.soItemId JOIN so ON so.id = si.soId LEFT JOIN sysuser u ON u.id = so.salesmanId LEFT JOIN stateconst sc ON sc.id = so.shipToStateId WHERE si.typeId IN (10,12) GROUP BY sc.code, u.userName ORDER BY state, revenue DESC;
Preview · demo data
| State | Salesperson | Revenue |
|---|---|---|
| CA | jsmith | $284,120.00 |
| CA | awong | $192,540.00 |
| TX | jsmith | $151,300.00 |
| NY | mlopez | $98,770.00 |
Notes & gotchas
- Uses postso / postsoitem (posted/fulfilled orders) so revenue is from completed transactions.
- typeId IN (10,12) keeps sale + drop-ship lines, excluding discounts/shipping.
- Easily extended with a date range (ps.postDate) or a country filter (so.shipToCountryId).
- Management view — not a full accounting report (no returns/credit memos).
What's our revenue by state and salesperson?
Agent Query session
I'll base this on posted sales orders so it reflects completed revenue, join the salesperson from so.salesmanId and the ship-to state from stateconst, and keep only sale and drop-ship line types.
revenue-by-state-salesperson.sql
SELECT COALESCE(sc.code,'Unknown') AS state, COALESCE(u.userName,'Unassigned') AS salesperson, ROUND(SUM(psi.totalPrice),2) AS revenue FROM postso ps JOIN postsoitem psi ON psi.postSOId = ps.id JOIN postsoitem si ON si.id = psi.soItemId JOIN so ON so.id = si.soId LEFT JOIN sysuser u ON u.id = so.salesmanId LEFT JOIN stateconst sc ON sc.id = so.shipToStateId WHERE si.typeId IN (10,12) GROUP BY sc.code, u.userName ORDER BY state, revenue DESC;
Agent Query session
Previewed on demo data, sorted by state then revenue. Want me to add a date range or filter to a single country?
| State | Salesperson | Revenue |
|---|---|---|
| CA | jsmith | $284,120.00 |
| CA | awong | $192,540.00 |
| TX | jsmith | $151,300.00 |
| NY | mlopez | $98,770.00 |
Want one like this for your Fishbowl?
Send a short brief — an ILC expert builds and tests it, and you get the file ready to import. The quote is free.