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:
ID_Reg
Name
ProductType
PackageType

I have another table called "Packages" with following columns:
ID_Pack
PackName
Weight

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

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


Example:

Entries in table "RegisteredProducts":
SQL
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":
SQL
ID_Pack PackName     Weight(kg)
1       Internatio      20
2       Domesta         15


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


So, the select statement shall be like this:

SQL
Product                 Weight shipped (kg)
  TV
   - Internatio                20
   - Domesta                   15
  - Subtotal                   35
  Stereo
   - Domesta                   15
  - Subtotal                   15
  Lamp
   - 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)
Posted
Updated 16-Aug-14 5:14am
v3
Comments
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.

SQL
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
 
Comments
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.

SQL
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


Output

C#
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