GROUP BY several elements

Question

0.00/5 (No votes)

See more:

SQL-Server-2008R2

, +

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 16-Aug-14 4:34am

Member 8179886

Updated 16-Aug-14 5:14am

v3

Add a Solution

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.

3 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

Ashi0891 · Accepted Answer · 2014-08-16T08:27:00

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 :)

Homero Rivera · Accepted Answer · 2014-08-16T08:35:00

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

Dilan Shaminda · Accepted Answer · 2014-08-16T08:45:00

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

GROUP BY several elements

3 solutions

Solution 1

Solution 2

Solution 3

Add your solution here

Preview 0