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 SUM(quantity)
  FROM MyTable
  WHERE prodcode = NewTable.prodcode

) AS TotalQuantity

FROM NewTable


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

Post Your Comment Posts/Links Rules



Add a RELEVANT link (not required)

Upload an image (not required)

No uploaded image
Real person number
Please enter the above number below

Admin Ren's Biking Blog