Home
SQL SUM of Non Normalised Column
Blog Date 16 August 2023
A Sample Table...
| id |
prodname |
prodcode |
quantity |
size |
colour |
| 1 |
ScrewDriver1 |
SCR001 |
0 |
small |
red |
| 2 |
ScrewDriver1 |
SCR001 |
5 |
small |
red |
| 3 |
ScrewDriver1 |
SCR002 |
3 |
small |
blue |
| 4 |
ScrewDriver1 |
SCR003 |
6 |
small |
green |
| 5 |
ScrewDriver1 |
SCR001 |
11 |
small |
red |
| 6 |
ScrewDriver1 |
SCR002 |
12 |
small |
blue |
And the code...
WITH NewTable AS
(SELECT DISTINCT prodname, prodcode, size, colour FROM MyTable)
SELECT
prodname,
prodcode,
size,
colour,
(
SELECT SUM(quantity)
FROM MyTable
WHERE prodcode = NewTable.prodcode
) AS TotalQuantity
FROM NewTable
Explanation...
The table is not normalised. Say what?
Note lines 1, 2, and 5. They are exactly the same item, ScrewDriver1, SCR001, small, and red. The ONLY difference is the quantity on each row (0, 5, and 11).
This type of table is a typical stock table. IE we have X number of item ScrewDriver1, SCR001, small, and red. So rather than 3 entries for small red ScrewDrivers we'd just have one... showing the total in stock. If we added or removed a small red ScrewDriver we'd update the one column to reflect this rather than adding a new column.
HOWEVER - this kind of non normalised table can arise if rather than a stock table we have records of small red ScrewDrivers coming a going. In an ideal world we'd have 2 tables. One with the records of small red ScrewDrivers coming and going and one with an ongoing total of small red ScrewDrivers in stock - IF we needed records of things coming and going.
But this is not an ideal world.
We have records of small red ScrewDrivers coming and going and NO overall stock table. Then the boss wants to know how many small red ScrewDrivers we have in stock at present.
In the SQL we use "WITH". Using "WITH" we create a table in memory - we're not writing this table to the DB think of it as a virtual table. Into this table we pour the DISTINCT rows for each type of ScrewDriver.
This creates the following virtual table (in this case named NewTable) -
| prodname |
prodcode |
size |
colour |
| ScrewDriver1 |
SCR001 |
small |
red |
| ScrewDriver1 |
SCR002 |
small |
blue |
| ScrewDriver1 |
SCR003 |
small |
gree |
Which is great! We have normalised the table... except for the quantity column. Hmmmm.
Well let us add a column... but this column is going to select from the actual MyTable on the server the sum of that column.
(
SELECT SUM(quantity)
FROM MyTable
WHERE prodcode = NewTable.prodcode
) AS TotalQuantity
But we need to know WHICH prodcode to SUM from... well that'll be the distinct "prodcode".
Reader's Comments
Name
Comment
Add a RELEVANT link (not required)
Upload an image (not required)
Uploading...
Home