# Select and Calculate

A WireBootstrap [DataTable](https://docs.wirebootstrap.com/wirebootstrap/reference/wire.data/wire.data.datatable) allows rows of data to be selected, aggregated, and transformed. 

## Select

The following example selects two columns, `product` and `price`, from the data table and creates a new data table using the `table` function at the end of the method chain.

```javascript
let data = [ 
    { product: "apples", order: "PRN001", quantity: 10, price: 5.5 },
    { product: "oranges", order: "PRN002", quantity: 15, price: 6 },
    { product: "bananas", order: "PRN002", quantity: 20, price: 7.5 }
];

let table = new wire.data.DataTable(data);
 
table = table.select("product", "price").table();
```

## Format

Columns have a  number of attributes that can be set in the `select` method chain.  In order to set these attributes, reference an individual column using the `column` method and then use any number of the [column](https://docs.wirebootstrap.com/wirebootstrap/reference/wire.data/wire.data.datatable/columns) attribute methods to set it's values.

Use the `as` function to change the name of a field.  In the example below, the field `quantity` will be named `number_of_skus` in the new table.

```javascript
...

table = table.select().column("quantity").as("number_of_skus").table();
```

### Title

Use the `title` function to give any column a descriptive title. This will be set into the `Title` property for the column in the resulting table.

```javascript
...

table = table.select().column("product").title("Product Title").table();
```

A data table needs a descriptive `Title` value for any column.  If one is not supplied, it will automatically create one.  In doing so, it will parse *camelCase* or *PascalCase* field names and intelligently capitalize and space accordingly to come up with a friendly title.

In the example below, a title for the `numberOfSkus` column is not supplied.  In the new table, the title will automatically be `Number Of Skus`.

```javascript
...

table = table.select().column("quantity").as("numberOfSkus").table();
```

### Number Formatting

To format numeric columns, use the format method with a format specification.  Valid values are *C\[N]*, *P\[N]*, *N\[N]* where *\[N]* is the number of decimal places.

The example below sets the formatting for the `price` column to currency one decimal place using the `C1` parameter in the `format` function.  This value will be set into the `Format` property for the column in the resulting table.

```javascript
...

table = table.select("product").column("price").format("C1").table();
```

### Custom Formatting

The format method can also accept a custom function in order to format data in the column.  

The following example formats a date field called `date_field` using the third party library [moment.js](https://momentjs.com).

```javascript
...

table = table.select().column("date_field").format((row) => {
    return moment(row["date_field"]).format("MM-DD-YYY");
}).table();
```

## Calculated Columns

Data tables allow calculated columns to be created on the fly.

The following creates two columns in a new data table that are the aggregates of columns in an existing data table.  In the new table, `quantity` will be aggregated using `sum`.  It will have the same name and be formatted to numeric one decimal place using `format` `N1`.  The `price` column will be averaged using `avg` and it's name changed to `avg_price`.  It will be formatted to currency one decimal place using `format` `C1`.

```javascript
...

table = table.select()
    .sum("quantity").format("N1")
    .avg("price").as("avg_price").format("C1")
    .table();
```

Aggregates can also be used with implicit grouping by selecting other columns to be included in the result. In the example below, the `order` column is included in the new data table, so the calculated column `quantity` is broken out by `order`.

```javascript
...

table = table.select("order").sum("quantity").table();
```

 Calculated columns can also be created using custom formulas using the `calc` method.

In the example below, a new column, `more_quantity`, is calculated as the `sum` of the `quantity` column times `10` and returned in a new table.  This is done using a custom function and a delegate `row`.  The function will also have the index position and all rows in the data table available as it is being evaluated.

```javascript
...

table = table.select()
    .column("more_quantity")
    .sum().calc((row, index, rows) => {
        return row["quantity"] * 10;
    })    .table();
```