how to count unique entries in database

Ask the community
,
1

I’m trying to count how many unique enteries in a database’s column… For example, If I have an entry for city, I want to return the number of different cities.
I would think to use .count() but how would “skip” cities entered more than once?

wixData.query("Faith-basedorganizations") 
   	.eq("city") 
 .count() 
    	.then((num) => { 
        		$w("# numCities ").text = "" + num; 
    	}) 
    	. **catch** ((error) => { 
        		**let**  errorMsg = error.message; 
        		**let**  code = error.code; 
   	 });
2

It’s not —> .count() <— you need!!!

You need → .distinct() <—
Inside of your data-query.

import wixData from 'wix-data';

wixData.query("myCollection")
  .distinct("state")
  .then((results) => {
    if(results.items.length > 0) {
      let items = results.items;
      let firstItem = items[0];
      let totalCount = results.totalCount;

    } else {
      // handle case where no matching items found
    }
  })
  .catch((error) => {
    let errorMsg = error.message;
    let code = error.code;
  });

Good luck!

3

Thanks! oddly it returns 10 cities, and when I count them there are 8 :thinking:

4

Show your code…

5

this is what I have currently. I’ve counted many times, there are 8 cities and 10 shows up…
@russian-dima

wixData.query("Faith-basedorganizations")
        .distinct("city")
        .then((results) => {
            if (results.items.length > 0) {
                let items = results.items;
                let firstItem = items[0];
                let totalCount = results.totalCount;
                $w("#numCities").text = totalCount.toString();

            } else {
                // handle case where no matching items found
            }
        })
        .catch((error) => {
            let errorMsg = error.message;
            let code = error.code;
        });
6

sorry for the late reply, bee out of office for a few weeks.

7

OK, note to self. a space after text in the conent manager databse is a different entery that one w/o the space. :man_facepalming:

8

:joy: so many trouble, because of a simple, typo, i know such situations xDD