Skip to content
Advertisement

SQL Having, but in Javascript way

So I came up here in the javascript to convert the SQL Query but using Javascript.

the following SQL statement lists the number of customers in each country. Only include countries with more than 5 customers:

SELECT COUNT(CustomerID), Country
FROM Customers
GROUP BY Country
HAVING COUNT(CustomerID) > 5;

please see my JS object of an array below and his expected output.

//my array
[
  {
    customerID: 1,
    CustomerName: "Alfreds Futterkiste",
    contactName: "Maria Anders",
    country: "Germany",
  },
  {
    customerID: 2,
    CustomerName: "Ana Trujillo Emparedados y helados",
    contactName: "Ana Trujillo",
    country: "Mexico",
  },
  {
    customerID: 3,
    CustomerName: " Antonio Moreno Taquería",
    contactName: "Antonio Mereno Jr.",
    country: "Mexico",
  },
];

//expected output
[
  {
    country: "Mexico",
    count: 2
  },
  {
    country: "Germany",
    count: 1
  },
]

We can use lodash or any library to solve this

Advertisement

Answer

We can use Array.reduce to get the desired result, this can give us a count for each customer by creating a map of our customers by country.

Once we have this, we can use Object.values to turn our map back into an array.

If we wish to filter on the count property, we can then do an Array.filter on the result data.

Lodash has a groupBy function which will do much the same thing.

const data = [{
    customerID: 1,
    CustomerName: "Alfreds Futterkiste",
    contactName: "Maria Anders",
    country: "Germany",
  },
  {
    customerID: 2,
    CustomerName: "Ana Trujillo Emparedados y helados",
    contactName: "Ana Trujillo",
    country: "Mexico",
  },
  {
    customerID: 3,
    CustomerName: " Antonio Moreno Taquería",
    contactName: "Antonio Mereno Jr.",
    country: "Mexico",
  },
];

const result = Object.values(data.reduce((map, customer) => {
  if (!map[customer.country]) {
    map[customer.country] = {
      country: customer.country,
      count: 0
    }
  }
  map[customer.country].count++;
  return map;
}, {}));


console.log("All results: ", result);

const countThreshold = 2;

console.log("Filtered by count (" + countThreshold + "): ", result.filter(r => r.count >= countThreshold));
User contributions licensed under: CC BY-SA
5 People found this is helpful
Advertisement