# ERP Selection Scoring SQL Generator: Weighted Vendor Ranking Query

> Source: https://netray.co/tools/erp-selection-scoring-sql-generator
> Publisher: Netray (netray.co), AI and integration engineering for ERP and manufacturing systems.
> Section: Free Tools

## Short answer

Creates the tables and weighted-average query needed to rank ERP vendors from committee scores, ready for SQL Server or PostgreSQL.

## Category

developer

## Audience

- ERP project lead
- Data analyst
- IT architect

## Tool

**Kind:** template

**Output:** code

**Language:** sql

**Fields:**

#### Schema name

**Key:** schema

**Type:** text

**Default:** erp_selection

**Help:** Schema that will hold the scoring tables.

#### Maximum score per criterion

**Key:** max_score

**Type:** number

**Default:** 5

#### Vendors to return

**Key:** top_n

**Type:** number

**Default:** 5

**Blocks:**

#### Create tables

**Body:** CREATE SCHEMA {{schema}};

CREATE TABLE {{schema}}.vendor (
  vendor_id    INT PRIMARY KEY,
  vendor_name  VARCHAR(120) NOT NULL
);

CREATE TABLE {{schema}}.criterion (
  criterion_id   INT PRIMARY KEY,
  category       VARCHAR(60) NOT NULL,
  criterion_name VARCHAR(160) NOT NULL,
  weight         DECIMAL(6,2) NOT NULL CHECK (weight > 0),
  is_mandatory   SMALLINT NOT NULL DEFAULT 0
);

CREATE TABLE {{schema}}.score (
  vendor_id    INT NOT NULL REFERENCES {{schema}}.vendor (vendor_id),
  criterion_id INT NOT NULL REFERENCES {{schema}}.criterion (criterion_id),
  scorer       VARCHAR(80) NOT NULL,
  score        DECIMAL(4,1) NOT NULL CHECK (score >= 0 AND score <= {{max_score}}),
  comment      VARCHAR(400) NULL,
  PRIMARY KEY (vendor_id, criterion_id, scorer)
);

#### Weighted ranking query

**Body:** WITH avg_score AS (
  SELECT vendor_id, criterion_id, AVG(score) AS avg_s
  FROM {{schema}}.score
  GROUP BY vendor_id, criterion_id
)
SELECT v.vendor_name,
       ROUND(100.0 * SUM(a.avg_s * c.weight) / (SUM(c.weight) * {{max_score}}), 1) AS weighted_pct
FROM avg_score a
JOIN {{schema}}.criterion c ON c.criterion_id = a.criterion_id
JOIN {{schema}}.vendor v ON v.vendor_id = a.vendor_id
GROUP BY v.vendor_name
ORDER BY weighted_pct DESC
FETCH FIRST {{top_n}} ROWS ONLY;

#### Mandatory criteria gate and category breakdown

**Body:** -- Vendors that scored below 2 on any mandatory criterion (average across scorers)
SELECT v.vendor_name, c.criterion_name, AVG(s.score) AS avg_s
FROM {{schema}}.score s
JOIN {{schema}}.criterion c ON c.criterion_id = s.criterion_id
JOIN {{schema}}.vendor v ON v.vendor_id = s.vendor_id
WHERE c.is_mandatory = 1
GROUP BY v.vendor_name, c.criterion_name
HAVING AVG(s.score) < 2;

-- Weighted percent by category
SELECT v.vendor_name, c.category,
       ROUND(100.0 * SUM(s.score * c.weight) / (SUM(c.weight) * {{max_score}}), 1) AS category_pct
FROM {{schema}}.score s
JOIN {{schema}}.criterion c ON c.criterion_id = s.criterion_id
JOIN {{schema}}.vendor v ON v.vendor_id = s.vendor_id
GROUP BY v.vendor_name, c.category
ORDER BY v.vendor_name, c.category;

**Notes:** Written in standard SQL with FETCH FIRST, which runs on PostgreSQL, Oracle 12c+ and SQL Server 2012+ (with ORDER BY). On MySQL replace FETCH FIRST with LIMIT. The category query averages every individual score row; if scorers differ in count per criterion, aggregate by scorer first. Review against your own engine before use.

## Content

**Intro:** A spreadsheet works for a three-vendor selection, but once many people score hundreds of criteria, a small database makes the maths auditable. This generator produces the schema and the ranking queries.

**Sections:**

#### How to use the generator

**Body:** Set a schema name, the top score on your scale and the number of vendors to return, then copy the three blocks. Run the first to create the tables, load vendors and criteria with weights, and have each committee member insert scores. The second block returns a ranking by weighted percentage. The third lists any vendor that failed a mandatory criterion and shows a category breakdown for the steering group.

#### How the weighted score works

**Body:** Each criterion's scores are averaged across committee members so that no single scorer dominates. The average is multiplied by the criterion weight, summed, and divided by the maximum possible weighted total. The result is a percentage that is easy to compare between vendors. Weights can be any positive numbers; they are normalized by the denominator, so they do not need to add up to 100.

**Bullets:**

- Average first, then weight
- Keep the same scale for all criteria
- Store comments next to scores for the audit trail

#### Mandatory gates and fairness

**Body:** A high total can hide a fatal gap. Flag mandatory criteria and review those separately from the weighted ranking so a vendor that cannot meet a compliance need does not win on strengths elsewhere. For fairness, agree weights before any demos, lock them in the criterion table, and record who scored what so the decision can be defended later.

#### Common mistakes

**Body:** The usual errors are changing weights after seeing results, scoring on different scales, and averaging category percentages rather than weighted scores. Also watch for missing scores: a vendor with fewer scored criteria looks artificially strong or weak depending on the formula. Require a complete score set before running the ranking.

**Cta:** If you want ERP data and selection evidence queried in plain language rather than SQL, Netray can show how that works. The generated SQL runs entirely on your own database.

**Faq:**

#### Which database does this SQL run on?

**Answer:** The schema and queries use standard SQL that runs on PostgreSQL and recent SQL Server and Oracle versions. For MySQL, replace the FETCH FIRST clause with LIMIT. Always test on a scratch schema before using it with real committee data.

#### Do weights need to add up to 100?

**Answer:** No. The query divides by the sum of weights times the maximum score, so any positive weights work. Using 100 as a total is simply convenient for explaining the model to stakeholders.

#### Why average scorers before weighting?

**Answer:** Averaging first gives each criterion one representative value, so a criterion scored by more people does not count for more than one scored by fewer. It also makes the result stable if scorers join late.

#### Can I use this for non-ERP selections?

**Answer:** Yes. The model is generic weighted scoring and works for any multi-criteria vendor or product evaluation. Rename the schema and adjust the categories to suit your project.

## Search terms

erp vendor scoring sql, weighted scoring model sql, vendor selection database, erp evaluation scoring query, weighted average sql ranking, rfp scoring database schema, erp selection scorecard database

---
Talk to an engineer: https://netray.co/contact?intent=erp_ai | Machine catalog: https://netray.co/agent/catalog.json