n3wbie

Array Function For Grouping Like mySql

8 posts in this topic

#1 ·  Posted (edited)

Hello M new To Forum But Using Autoit over a year I Was Stuck When I Wanted To Group Down Elements Of Array in Autoit Than Came Across This Post  

https://www.autoitscript.com/forum/topic/42172-group-elements-in-array/#comment-314397

 

But There wasnt any reply Probably I will Re explain Scenerio Suppose i Have a data where in there are 5 teams Red Blue Green yellow ... and data table consist of data like name of person and much more details Now Basically

I Want data to be consolidated To Teams Statistics Like Team a has 10 Participants, average age of participants etc For This Mysql Has A Command Group by Like I could Do It 'Select Count(*) ,avg(age) from table group by team_name Here There Is Scenario wherein I Know That There Are 5 teams But In My Data I Dont So I Want To Group It Can U Please Help Me Sort It Down 

Also If You Cant Understand My Problem I Have Attached An Example Spreadsheet Which Elaborates Whats Input And Output I Require Just Thing Is I Dont Want Excel To Get Involved In This

 

 

example.xlsx

Edited by n3wbie

Share this post


Link to post
Share on other sites



Sqllite 

Ok Havnt Tried It Will Look Into But Can This Type Of Sorting Be Done There ?

And Do I Need To Install Some Additional Things In Order To Work With SqlLite Or It Works with Autoit standalone?

Share this post


Link to post
Share on other sites

SQLite is generally on most windows machines, and is standalone database which will work with most SQL like statements. I believe it is downloaded if not present on users machine.

Just look in helpfile on index tab for _SQLite* functions.


AutoIt Absolute Beginners    Require a serial    Pause Script    Video Tutorials by Morthawt   ipify 

Monkey's are, like, natures humans.

Share this post


Link to post
Share on other sites

Look at _SQLite_* functions in help: zero installation and zero maintenance.

Yes, SQLite is a pretty useful embedded (i.e. not client-server) SQL engine which offers most of SQL features, of course including group by, order by, aggregate functions, and much more.


This wonderful site allows debugging and testing regular expressions (many flavors available). An absolute must have in your bookmarks.
Another excellent RegExp tutorial. Don't forget downloading your copy of up-to-date pcretest.exe and pcregrep.exe here
RegExp tutorial: enough to get started
PCRE v8.33 regexp documentation latest available release and currently implemented in AutoIt beta.

SQLitespeed is another feature-rich premier SQLite manager (includes import/export). Well worth a try.
SQLite Expert (freeware Personal Edition or payware Pro version) is a very useful SQLite database manager.
An excellent eBook covering almost every aspect of SQLite3: a must-read for anyone doing serious work.
SQL tutorial (covers "generic" SQL, but most of it applies to SQLite as well)
A work-in-progress SQLite3 tutorial. Don't miss other LxyzTHW pages!
SQLite official website with full documentation (may be newer than the SQLite library that comes standard with AutoIt)

Share this post


Link to post
Share on other sites

Thanks Guys For Prompt Reply's

But Still M Thinking Doing It Not Involving sqllite But I Will Work Down 

The Only Problem That Arises For Me Using Sql Lite Is ITs A File Based Database Ie It isnt A object Oriented So It Slows Down If More Records Are To be Sorted

So If You have Any ALternative Please Suggest  My Records Would Have Min 6000 Records And Max 8000

SO if any other solution Please Do Suggest

Share this post


Link to post
Share on other sites

6000 rows is a ridiculously small DB; 6 million rows is average, 6 billions rows is largish.

Contrary to what you think, an SQLite DB can be memory- or disk-based.

I also don't understand why you believe that being object-oriented anyhow helps efficiency over scaling.

(Can you please stop using emphasis uppercase, that makes your posts hard to read.)


This wonderful site allows debugging and testing regular expressions (many flavors available). An absolute must have in your bookmarks.
Another excellent RegExp tutorial. Don't forget downloading your copy of up-to-date pcretest.exe and pcregrep.exe here
RegExp tutorial: enough to get started
PCRE v8.33 regexp documentation latest available release and currently implemented in AutoIt beta.

SQLitespeed is another feature-rich premier SQLite manager (includes import/export). Well worth a try.
SQLite Expert (freeware Personal Edition or payware Pro version) is a very useful SQLite database manager.
An excellent eBook covering almost every aspect of SQLite3: a must-read for anyone doing serious work.
SQL tutorial (covers "generic" SQL, but most of it applies to SQLite as well)
A work-in-progress SQLite3 tutorial. Don't miss other LxyzTHW pages!
SQLite official website with full documentation (may be newer than the SQLite library that comes standard with AutoIt)

Share this post


Link to post
Share on other sites

you can play a bit with both ways, that is: by using only _Array* functions or by involving Sqlite to our rescue. Here two examples to play with:
1) By using only _Array* functions (basically _ArrayUnique() and _ArrayFindAll() )

#include <array.au3>
local $aInput[27][4] = [['Name', 'Team', 'Age', 'Salary'], _
        ['A', 'Blue', '41', '98658'], _
        ['B', 'Red', '40', '54406'], _
        ['C', 'Green', '33', '74489'], _
        ['D', 'Yellow', '36', '57857'], _
        ['E', 'Red', '34', '55565'], _
        ['F', 'Blue', '33', '83666'], _
        ['G', 'Yellow', '44', '66627'], _
        ['H', 'Red', '40', '63970'], _
        ['I', 'Red', '49', '89346'], _
        ['J', 'Blue', '37', '79437'], _
        ['K', 'Red', '41', '79038'], _
        ['L', 'Yellow', '31', '84754'], _
        ['M', 'Red', '46', '96611'], _
        ['N', 'Blue', '43', '86699'], _
        ['O', 'Red', '32', '94452'], _
        ['P', 'Red', '39', '77050'], _
        ['Q', 'Yellow', '31', '76837'], _
        ['R', 'Blue', '45', '59680'], _
        ['S', 'Red', '50', '72844'], _
        ['T', 'Blue', '42', '90642'], _
        ['U', 'Red', '48', '83451'], _
        ['V', 'Blue', '38', '60356'], _
        ['W', 'Red', '37', '70999'], _
        ['X', 'Yellow', '46', '70282'], _
        ['Y', 'Red', '47', '69867'], _
        ['Z', 'Red', '48', '57109']]

_ArrayDisplay($aInput, "Input as array")
Local $ii
$aUniques = _ArrayUnique($aInput, 1, 1, 0, 0)
Local $aOutputs[UBound($aUniques) + 1][UBound($aInput, 2)] ; make room for output data

For $i = 0 to UBound($aUniques) - 1 ; scan all uniques
    $ii = $i + 1 ; do not use row 0 of $aOutputs (shift all rows by 1), row 0 will be used for titles of columns
    $aIndexes = _ArrayFindAll($aInput, $aUniques[$i], 1, 0, 0, 0, 1) ; rows of $aInput containing only the unique nr. $i
    $aOutputs[$ii][1] = $aInput[$aIndexes[0]][1] ; the name of this unique goes to column 1 of $aOutputs
    for $i2 = 0 to UBound($aIndexes) - 1 ; scan all rows containing this "team" value
        $aOutputs[$ii][2] += $aInput[$aIndexes[$i2]][2] ; sum all ages (column 2)
        $aOutputs[$ii][3] += $aInput[$aIndexes[$i2]][3] ; sum all salary (column 3)
    Next
    $aOutputs[$ii][2] = Round($aOutputs[$ii][2] / UBound($aIndexes), 5) ; average of ages
Next ; next group of uniques
for $i = 0 to UBound($aInput, 2) - 1 ; titles of columns
    $aOutputs[0][$i] = $aInput[0][$i]
Next

_ArrayDisplay($aOutputs, "Outputs 1 & 2")

2) by using an udf that will facilitate you allowing the use of SQL (nearly) directly on arrays:

; by sql
#include <array.au3>
#include <ArraySQL.au3> ; <-- https://www.autoitscript.com/forum/topic/166536-manage-arrays-by-means-of-sql/#comment-1234441

Local $aInput[26][4] = [['A', 'Blue', '41', '98658'], _
        ['B', 'Red', '40', '54406'], _
        ['C', 'Green', '33', '74489'], _
        ['D', 'Yellow', '36', '57857'], _
        ['E', 'Red', '34', '55565'], _
        ['F', 'Blue', '33', '83666'], _
        ['G', 'Yellow', '44', '66627'], _
        ['H', 'Red', '40', '63970'], _
        ['I', 'Red', '49', '89346'], _
        ['J', 'Blue', '37', '79437'], _
        ['K', 'Red', '41', '79038'], _
        ['L', 'Yellow', '31', '84754'], _
        ['M', 'Red', '46', '96611'], _
        ['N', 'Blue', '43', '86699'], _
        ['O', 'Red', '32', '94452'], _
        ['P', 'Red', '39', '77050'], _
        ['Q', 'Yellow', '31', '76837'], _
        ['R', 'Blue', '45', '59680'], _
        ['S', 'Red', '50', '72844'], _
        ['T', 'Blue', '42', '90642'], _
        ['U', 'Red', '48', '83451'], _
        ['V', 'Blue', '38', '60356'], _
        ['W', 'Red', '37', '70999'], _
        ['X', 'Yellow', '46', '70282'], _
        ['Y', 'Red', '47', '69867'], _
        ['Z', 'Red', '48', '57109']]

_ArrayDisplay($aInput, "Input as array")

$sQuery = "Select column1,avg(column2), sum(column3) from array group by column1;"
$aResult = _ArraySQL($aInput, $sQuery)
_ArrayDisplay($aResult)

 

1 person likes this

small minds discuss people average minds discuss events great minds discuss ideas.... and use AutoIt....

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!


Register a new account

Sign in

Already have an account? Sign in here.


Sign In Now