Sql-server – Create a sub query for sum data as a new column in SQL Server

sql servert-sql

Suppose that I have a table named tblTemp which has this data:

|  ID | AMOUNT |
----------------
|   1 |     10 |
| 1-1 |     20 |
| 1-2 |     30 |
| 1-3 |     40 |
|   2 |     50 |
|   3 |     60 |
|   4 |     70 |
| 4-1 |     80 |
|   5 |     90 |
|   6 |    100 |

ID will be formatted as X (without a dash) if it's only one ID or (X-Y) if it's a new ID (Y) is child of (X).

I want to add a new column (Total Amount) to output as below:

|  ID | AMOUNT |   Total Amount |
---------------------------------
|   1 |     10 |        100     |
| 1-1 |     20 |        100     |
| 1-2 |     30 |        100     |
| 1-3 |     40 |        100     |
|   2 |     50 |         50     |
|   3 |     60 |         60     |
|   4 |     70 |        150     |
| 4-1 |     80 |        150     |
|   5 |     90 |         90     |
|   6 |    100 |        100     |

The "Total Amount" column is calculated as the sum of the amounts column for the same X ID.

In order to get the parent ID (X), I use the following SQL:

SELECT
  ID, SUBSTRING (ID, 1,
                 IIF (CHARINDEX('-', ID) = 0,
                        len(ID),
                        CHARINDEX('-', ID) - 1)
  ), Amount
FROM
  tblTemp

How can I write a query like this in SQL Server 2012?

You can use sqlfiddle here to test it.

Best Answer

What you're looking for is called a window function. It's similar to grouping, but it allows you to perform aggregate calculations on sets of records without actually grouping. Searching for "window function" or "over clause" should get you lots of helpful reading material.

select
  [ID]
  ,Amount
  ,[Total Amount] = sum(Amount) over(
    partition by left([ID],charindex('-',ID + '-') - 1)
    )
  from tblTemp

Related Solutions

SQL Server – How to Create One-to-Many Relationship with Privileged Child

Another way (without Nulls and without cycles in the FOREIGN KEY relationships) is to have a third table to store the "favourite children". In most DBMS, you'll need an additional UNIQUE constraint on TableB.

@Aaron was faster to identify that the naming convention above is rather cumbersome and can lead to errors. It's usually better (and will keep you sane) if you don't have Id columns all over your tables and if the columns (that are joined) have same names in the many tables that appear. So, here's a renaming:

Parent
    ParentID        INT NOT NULL PRIMARY KEY

Child
    ChildID         INT NOT NULL PRIMARY KEY
    ParentID        INT NOT NULL FOREIGN KEY REFERENCES Parent (ParentID)
    UNIQUE (ParentID, ChildID)

FavoriteChild
    ParentID        INT NOT NULL PRIMARY KEY
    ChildID         INT NOT NULL 
    FOREIGN KEY (ParentID, ChildID) 
        REFERENCES Child (ParentID, ChildID)

In SQL-Server (that you are using), you also have the option of the IsFavorite bit column you mention. The unique favourite child per parent can be accomplished via a filtered Unique Index:

Parent
    ParentID        INT NOT NULL PRIMARY KEY

Child
    ChildID         INT NOT NULL PRIMARY KEY
    ParentID        INT NOT NULL FOREIGN KEY REFERENCES Parent (ParentID)
    IsFavorite      BIT NOT NULL

CREATE UNIQUE INDEX is_FavoriteChild
  ON Child (ParentID)
  WHERE IsFavorite = 1 ;

And the main reason that your option 1 is not recommended, at least not in SQL-Server, is that the pattern of circular paths in the foreign key references has some problems.

Read a quite old article: SQL By Design: The Circular Reference

When inserting or deleting rows from the two table, you'll run into the "chicken-and-egg" problem. Which table should I insert first - without violating any constraint?

In order to solve that, you have to define at least one column nullable. (OK, technically you don't have to, you can have all columns as NOT NULL but only in DBMS, like Postgres and Oracle, that have implemented deferrable constraints. See @Erwin's answer in a similar question: Complex foreign key constraint in SQLAlchemy on how this can be done in Postgres). Still, this setup feels like skating on thin ice.

Check also an almost identical question at SO (but for MySQL) In SQL, is it OK for two tables to refer to each other? where my answer is pretty much the same. MySQL has no partial indexes though, so the only viable options are the nullable FK and the extra table solution.

Sql-server – SQL Server database design for “archived but available” data

For convenience, assume that the live database is called LiveDb and the achive database is called ArchiveDb

Add a UNION ALL view in LiveDb pointing to the tables in ArchiveDb database via a synonym (There is no need to do a combined db with synonyms)
"Partition" on visit.date and denormalise this column to visit_payments too if it isn't there already (this improves co-located join performance)
Only archive the two large tables if possible (reduces chance of tripping up the optimiser). Keep the UNION ALL view and the other tables in LiveDb so all joins to the smaller tables are kept local
Add a CHECK constraint on the tables in both LiveDb and ArchiveDb that describes the range of visit.date contained in the table. This helps the optimiser eliminate the archive table from both seeks and scans that contain the column visit.data. You will have to periodically update this constraint.
In the UNION ALL view, add a WHERE criteria which filters on visit.data. This is in addition to the hint you already provided in the check constraint. This maximises the chance of filters being pushed down
If you have EE, partition the table in the archive database (But NOT in the live database). If you want to get really fancy, use filegroup level backup/restore of the archive databases to save on backup times.
Consider putting AchiveDb in SIMPLE recovery mode if it isn't already. You are not likely to need transaction log backups of ArchiveDb
Use INSERT... WITH (TABLOCK) SELECT ... WITH (ROWLOCK) to force minimal logging on the destination when moving data between LiveDb and ArchiveDb

All of the above does not guarantee that the optimiser will eliminate the archive tables from seeks and scans, but it makes it more likely.

When the elimination doesn't happen. These are the effect you may see (this list may be incomplete). For seeks, you will get an additional seek on every query (this drives up IOPS). For scans, the results could be disastrous for performance as you may end up scanning both the archive and live tables. Here are the typical ways you can trip up the optimiser:

If you join the visit% tables together and don't include the visit.data in the join criteria (this is why you want to denormalise). Because of this, you may wish to modify some of your queries
If you get a hash join between visit.data and another table (for example a date dimension), you may not get the right elimination of tables
If you try to aggregate data over the archived tables
If you filter on anything BUT the visit.data, for example a seek directly on the key of the view.

For the last scenario, you can guard yourself against the worst effects by adding another check constraint on the cid - if this is possible. You did mention that the sequence of cid not "clean" with respect to the dates and progression of rows in the table. However, could you maintain a table that contains the information: "There are no cid above this number since this visit.data" or similar? This could then drive an additional constraint.

Another thing to be careful about is that parallel queries may spawn a LOT more threads once you query the partitioned view (as both "sub-tables" will be exposed to the same parallel optimisations). For that reasons, you may want to limit MAXDOP on the server or the queries that are parallel.

By the way, if you know the queries well - you may not even need the same indexes in the two databases (this assumes you are 100% sure you will get the right elimination of tables). You could even consider using column stores for ArchiveDb.

Best Answer

Related Solutions

SQL Server – How to Create One-to-Many Relationship with Privileged Child

Sql-server – SQL Server database design for “archived but available” data

Related Question