Sum in Script

[evansabres]

I have a large data set, of which some of the important fields are event_name and [Net Sold Qty]

For a particular event_name, any time a ticket is sold, a row of data is created:

event_name               Net Sold Qty

ABC123                         2

ABC123                         3

DEF456                         7

DEF456                         5

GHI987                         4

GHI987                         4

and so on.

What I would like to be able to do in my script is to aggregate all of the [Net Sold Qty] for an event_name prior to be loading into my application. So that when I view the data, it would appear as

event_name          Net Sold Qty

ABC123                    5

DEF456                    10

GHI987                         8

How would this be done in the script? I can certainly do this within the application but would like to do this in the script for the purpose of this particular application.

[neelamsaroha157]

Load event_name,

         Sum([Net Sold Qty]) as NetSoldQtySum

resident yourtablename

group by event_name;

[vishsaggi]

May be try this:

SourceTable:

LOAD event_name, NetSoldQty

From Source;

LEFT JOIN(SourceTable)

LOAD event_name,

           Sum(NetSoldQty) AS SaleQty

Resident SourceTable

Group By event_name;

[evansabres]

This is currently the Structure of my script. Where would I add in your recommendation or otherwise alter my script?

Ticketing:

LOAD

[Net Sold Qty] AS bufQTYTTL,

purchasePRICE AS bufPurchPrice,

block_purchase_price AS bufREV,

transDATE AS bufTRANSDTE,

PriceCode AS bufPriceCDE,

team AS bufOpp,

LicenseRev AS bufLICREV,

event_date AS bufEventDTE,

event_time AS bufEventTME,

event_name AS bufEVENTNME,

comp_name AS bufCMPNME,

compTYPE AS bufCMPTYPE

FROM [DATA SOURCE];

LEFT JOIN (Ticketing)

Schedule:

LOAD

event_name AS bufEVENTNME,

      WEEKDAY,

      MONTH,

      DAY,

      YEAR,

      TIME,

      [Game Date] AS    bufGMEDTE,

      Opponent AS    bufGMEDESC,

      LOCATION,

      CITY,

      ST,

      TYPE,

      [VAR. PRICE]

FROM [DATA SOURCE];

TicketingNew:

LOAD

bufGMEDTE,

bufEVENTNME,

bufGMEDESC,

bufQTYTTL,

bufREV,

'buf' AS brandNME

RESIDENT 'Ticketing';

DROP TABLE 'Ticketing';

[vishsaggi · Accepted solution]

Try this?

Ticketing:

LOAD

[Net Sold Qty] AS bufQTYTTL,

purchasePRICE AS bufPurchPrice,

block_purchase_price AS bufREV,

transDATE AS bufTRANSDTE,

PriceCode AS bufPriceCDE,

team AS bufOpp,

LicenseRev AS bufLICREV,

event_date AS bufEventDTE,

event_time AS bufEventTME,

event_name AS bufEVENTNME,

comp_name AS bufCMPNME,

compTYPE AS bufCMPTYPE

FROM [DATA SOURCE];

LEFT JOIN (Ticketing)

Schedule:

LOAD

event_name AS bufEVENTNME,

      WEEKDAY,

      MONTH,

      DAY,

      YEAR,

      TIME,

      [Game Date] AS    bufGMEDTE,

      Opponent AS    bufGMEDESC,

      LOCATION,

      CITY,

      ST,

      TYPE,

     [VAR. PRICE]

FROM [DATA SOURCE];

NoConcatenate

TicketingNew:

LOAD

bufGMEDTE,

bufEVENTNME,

bufGMEDESC,

bufQTYTTL,

bufREV,

'buf' AS brandNME

RESIDENT 'Ticketing';

DROP TABLE 'Ticketing';

LEFT JOIN(TicketingNew)

LOAD bufEVENTNME,

     Sum(bufQTYTTL) AS SumQTYTTL

Resident TicketingNew

Group By bufEVENTNME;

[evansabres]

This worked great, thank you.