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
COUNT UNIQUE VALUES
-
- Posts: 7532
- Joined: Sun Apr 24, 2005 12:36 am
- Contact:
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
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
-
- Posts: 595
- Joined: Sun Apr 24, 2005 2:22 am
- Contact:
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.
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
Re: COUNT UNIQUE VALUES
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?
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
Re: COUNT UNIQUE VALUES
Hi Rod,
What's wrong with the solution support offered?
What's wrong with the solution support offered?
Tom - V8.8 build 3137 - MySql / PostGres
Re: COUNT UNIQUE VALUES
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.
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
Re: COUNT UNIQUE VALUES
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
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
Jaymer
Aware Programming & Consulting - Tampa FL
Re: COUNT UNIQUE VALUES
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
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
Re: COUNT UNIQUE VALUES
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
Know them, love them ,use them
Re: COUNT UNIQUE VALUES
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
- Screenshot 2020-06-29 at 12.04.08.png (30.34 KiB) Viewed 84506 times
AwareIM Developer edition. Version 8.5 (Build 2827) running on Windows Server 2012 R2 Standard
Re: COUNT UNIQUE VALUES
@Ddumas
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?
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
Tom - V8.8 build 3137 - MySql / PostGres
Re: COUNT UNIQUE VALUES
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
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