All examples
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.

Type: Query Output: TableData: Demo sandbox

What people ask

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

4 rows · demo data
StateSalespersonRevenue
CAjsmith$284,120.00
CAawong$192,540.00
TXjsmith$151,300.00
NYmlopez$98,770.00

Notes & gotchas

A locked session — the real conversation that produced this report. Open it in the app to fork and continue.
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?

preview · demo data
StateSalespersonRevenue
CAjsmith$284,120.00
CAawong$192,540.00
TXjsmith$151,300.00
NYmlopez$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.

Related examples