COUNT UNIQUE VALUES

Contains tips for configurators working with Aware IM
Post Reply
czillman
Posts: 22
Joined: Fri Nov 18, 2005 5:39 pm
Location: Raleigh, NC
Contact:

COUNT UNIQUE VALUES

Post by czillman »

Is there an elegant way to COUNT UNIQUE VALUES of an attribute?

I think this is the SQL
SELECT COUNT(DISTINCT column_name) FROM table_name

I cant figure out the bus rule
BO.CNTUNIQUEPARTNUMBERS = SELECT COUNT(DISTINCT BO.PARTSLIST.PARTNUMBER) FROM BO.PARTSLIST
aware_support
Posts: 7523
Joined: Sun Apr 24, 2005 12:36 am
Contact:

Post by aware_support »

Unfortunately, right now this can only be accomplished as a Java plug-in.
Aware IM Support Team
tford
Posts: 4238
Joined: Sat Mar 10, 2007 6:44 pm

Post by tford »

czillman,

If you are really desperate to do this & not into Java plug-in, you could keep a BO with unique values of a certain attribute using a method like posted at http://www.awareim.com/forum/viewtopic. ... ght=unique . You have to really be careful about accounting for all add/change/delete activity though. It seems complicated but it's not a ton of work. I would not want to use this method for very many attributes though.

After accumulating the unique attributes, you could then easy COUNT them.

Tom
czillman
Posts: 22
Joined: Fri Nov 18, 2005 5:39 pm
Location: Raleigh, NC
Contact:

Post by czillman »

Thanks for the replies.

I've never written a java plug-in. What would I use to write the plug-in? Is there some sort of compiler that would be good for little simple thinkes like this?

cheers, charles
aware_support2
Posts: 595
Joined: Sun Apr 24, 2005 2:22 am
Contact:

Post by aware_support2 »

Another idea is to maintain a unique parts list in addition to the full parts list. This could be done with the following rules on BO:

Add unique part:
If Part WAS ADDED TO BO.PartsList AND NOT(EXISTS Part WHERE (Part.PartNumber = AddedPart.PartNumber AND Part IN BO.UniquePartsList))
Then INSERT AddedPart IN BO.UniquePartsList

Remove unique part:
If Part WAS REMOVED FROM BO.PartsList AND NOT(EXISTS Part WHERE (Part.PartNumber = RemovedPart.PartNumber AND Part IN BO.PartsList))
Then
FIND Part WHERE Part.PartNumber = RemovedPart.PartNumber AND Part IN BO.UniquePartsList
REMOVE Part FROM BO.UniquePartsList


Then you can count the unique parts with the following rule:

BO.CntUniquePartNumbers = COUNT Part WHERE Part IN BO.UniquePartsList


This is similar to the approach mentioned above, except that it is limited in scope to a given object instance, does not require additional objects and takes care of removed list members.
Aware IM Support Team
RLJB
Posts: 914
Joined: Tue Jan 05, 2010 10:16 am
Location: Sydney, Australia

Re: COUNT UNIQUE VALUES

Post by RLJB »

Has anyone managed to come up with a nice solution for this in the last 10 years?

We need to count unique instances, based on an attribute, any ideas? A custom function perhaps?
Rod. Aware 8.6 (latest build), Developer Edition, on OS Linux (Ubuntu) using GUI hosted on AWS EC2, MYSQL on AWS RDS
tford
Posts: 4238
Joined: Sat Mar 10, 2007 6:44 pm

Re: COUNT UNIQUE VALUES

Post by tford »

Hi Rod,

What's wrong with the solution support offered?
Tom - V8.8 build 3137 - MySql / PostGres
RLJB
Posts: 914
Joined: Tue Jan 05, 2010 10:16 am
Location: Sydney, Australia

Re: COUNT UNIQUE VALUES

Post by RLJB »

Hi Tom

This is for a table/BO that is pretty large (it's a learning management system records store), so I want to avoid duplicating a massive table with more data.
Rod. Aware 8.6 (latest build), Developer Edition, on OS Linux (Ubuntu) using GUI hosted on AWS EC2, MYSQL on AWS RDS
Jaymer
Posts: 2430
Joined: Tue Jan 13, 2015 10:58 am
Location: Tampa, FL
Contact:

Re: COUNT UNIQUE VALUES

Post by Jaymer »

FWIW...
I had no idea what a "learning management system records store" was.
A simple google search showed some sponsored ads.

Saw a neat demo - COULD have been aware in the 1st 10 seconds (if you were REALLY good), then became apparent it was something else. VERY nice! Since we're all "in the biz", I'm sure someone could learn something (UX-wise) from this:

https://www.absorblms.com/features/

I looked at all 10 products on this page:
http://www.capterra.com/sem-compare/lea ... oCJaDw_wcB
and the Absorb product (linked above) was the only one that lets you see a ton of real screens so I didn't learn much from the other systems.

FYI
Click Here to see a collection of my tips & hacks on this forum. Or search for "JaymerTip" in the search bar at the top.

Jaymer
Aware Programming & Consulting - Tampa FL
RLJB
Posts: 914
Joined: Tue Jan 05, 2010 10:16 am
Location: Sydney, Australia

Re: COUNT UNIQUE VALUES

Post by RLJB »

Funny how the same need keeps arising... 3 years laters... does anyone have a nice function or solution for this yet, that isn't:

a. a stored procedure
b. creating another table of unique entries

tks
Rod. Aware 8.6 (latest build), Developer Edition, on OS Linux (Ubuntu) using GUI hosted on AWS EC2, MYSQL on AWS RDS
ddumas
Posts: 389
Joined: Tue Apr 23, 2013 11:17 pm

Re: COUNT UNIQUE VALUES

Post by ddumas »

USE EXEC_SP, or the EXEC_SPF() function to return the value. I call stored procedures throughout my application, especially for queries. Also to set flags or calculated columns that are best left to SQL code
Know them, love them ,use them :)
yahya
Posts: 77
Joined: Sat Jul 16, 2011 6:00 am
Location: South Africa
Contact:

Re: COUNT UNIQUE VALUES

Post by yahya »

What happens when you want to just display the unique values in a list? See my screenshot as an example.
Attachments
List showing duplicated values in a list
List showing duplicated values in a list
Screenshot 2020-06-29 at 12.04.08.png (30.34 KiB) Viewed 63156 times
AwareIM Developer edition. Version 8.5 (Build 2827) running on Windows Server 2012 R2 Standard
tford
Posts: 4238
Joined: Sat Mar 10, 2007 6:44 pm

Re: COUNT UNIQUE VALUES

Post by tford »

@Ddumas

Code: Select all

USE EXEC_SP, or the EXEC_SPF() function to return the value. I call stored procedures throughout my application, especially for queries. Also to set flags or calculated columns that are best left to SQL code
Know them, love them ,use them
Could you provide a couple examples of your use cases of USE EXEC_SP and EXEC_SPF() functions to return a value .. for queries, set flags, calculated columns?
Tom - V8.8 build 3137 - MySql / PostGres
jannes
Posts: 100
Joined: Tue Jul 02, 2019 12:22 pm

Re: COUNT UNIQUE VALUES

Post by jannes »

See this example :

The process :
EXEC_SP `DoCount` WITH `@regel`='SELECT count(DISTINCT plaats) FROM persoon'
FIND All Msg
DISPLAY MESSAGE Msg.Regel

The stored procedure in the DB :
CREATE DEFINER=`root`@`localhost` PROCEDURE `devtest`.`docount`(IN regel varchar(2000))
BEGIN
DELETE FROM msg;
EXECUTE IMMEDIATE concat ('INSERT INTO msg (id, regel) values (1, (', regel, '))');
END
tford
Posts: 4238
Joined: Sat Mar 10, 2007 6:44 pm

Re: COUNT UNIQUE VALUES

Post by tford »

Thank you!
Tom - V8.8 build 3137 - MySql / PostGres
Post Reply