Click here to Skip to main content
15,917,862 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
I have a table called "RegisteredProducts" with following columns:

I have another table called "Packages" with following columns:

The last table is called "Products" with following columns:

How do I write a query that will select all entries from "Registered Products", group them by ProductType and PackageType, and also sum the total weight of the selected products? In table "Packages", there is a column "Weight" that differs between package types.

Links between tables:
ProductType -> ID_Product
PackageType -> ID_Pack


Entries in table "RegisteredProducts":
ID_Reg Name ProductType PackageType
1    Sony       1           2
2    Asus       2           2
3    Asus       1           1
4    Albie      3           1

Entries in table "Packages":
ID_Pack PackName     Weight(kg)
1       Internatio      20
2       Domesta         15

Entries in table "Products":
ID_Product ProdName
1             TV
2           Stereo
3            Lamp

So, the select statement shall be like this:

Product                 Weight shipped (kg)
   - Internatio                20
   - Domesta                   15
  - Subtotal                   35
   - Domesta                   15
  - Subtotal                   15
   - Internatio                20
  - Subtotal                   20

TOTAL                          70

So basically, just sum the total weight for each product and package type, with subtotals and totals of possible.

(Updated the post with an example)
Updated 16-Aug-14 5:14am
OriginalGriff 16-Aug-14 10:50am    
Can you give us a sample of the output you want - a couple of rows should do it.
The way I read it at the moment, the output either isn't clear or is unlikely: all entries with the total? You want the total 8 times if there are 8 "registered products" in the same "package"?
Member 8179886 16-Aug-14 11:06am    
Look above in the original post for an updated example.

Use pivot in sql query to get the required result. Please refer this link SQL - Pivot with Grand Total Column and Row

Result will be something like this:
product Internatio Domesta  Sub Total
tv      20         15         35
stereo             15         15
lamp    20                    20
Total                         70

you can also look up this link[^]

lemme know if you get any problem and also if it works :)
Share this answer
FYI - Your tables are not following some basic standards, such as using the same column name for things that are meant to be the same (e.g. RegisteredProducts.PackageType and Packages.PackageType).
Be careful with that.
also, the result set you want to see looks more like an Excel pivot to me.
A similar result is possible, but not a verbatim one.

select pr.ProdName, pa.PackName, SUM(pa.Weight) as TotalWeight 
from  RegisteredProducts rp
      inner join Packages pa on rp.PackageType = pa.ID_Pack
      inner join Products pr on rp.ProductType = pr.ID_Product
group by pr.ProdName, pa.PackName with Rollup
Share this answer
Jörgen Andersson 16-Aug-14 14:39pm    
:thumbsup: for pointing out the naming standard problem.
Hope this will help you too. You can use ROLLUP operator and GROUPING to get the similar output as you mentioned.This is not exactly same as your output.But this might help you.

SELECT CASE WHEN (GROUPING(Products.ProdName) = 1) THEN 'Total'
            ELSE ISNULL(Products.ProdName, 'UNKNOWN')
       END AS Product,
       CASE WHEN (GROUPING(Packages.PackName) = 1) THEN 'SubTotal'
            ELSE ISNULL(Packages.PackName, 'UNKNOWN')
       END AS Packages,
       SUM(Packages.weight) AS Weight_shipped
From RegisteredProducts INNER JOIN Products ON RegisteredProducts.ProductType = Products.ID_Product
    INNER JOIN Packages ON RegisteredProducts.PackageType = Packages.ID_Pack
GROUP BY Products.ProdName, Packages.PackName WITH ROLLUP


Product Packages        Weight_Shipped
Lamp	Internatio	20
Lamp	SubTotal	20
Stereo	Domesta	        15
Stereo	SubTotal	15
TV	Domesta 	15
TV	Internatio	20
TV	SubTotal	35
Total	SubTotal	70
Share this answer

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900