Grouping Data

Question

Trench37716 0 Newbie Poster

9 Years Ago

I am calling data from my database but the result is not what I want.
Below is my code:

Select distinct concat(ImporterDistributor.name, '/', ID.name) as [Distributor_Importer],
 case when Product_Delivery.Product=1 then isnull(max(Product_Delivery.Opening),0) end as OpeningPMS,
 case when Product_Delivery.Product=2 then isnull(max(Product_Delivery.Opening),0) end as OpeningAGO,
 case when Product_Delivery.Product=3 then isnull(max(Product_Delivery.Opening),0) end as OpeningATK
  from ImporterDistributor  
 join
 Product_Delivery on (ImporterDistributor.id=Product_Delivery.Distributor and Product_Delivery.Transaction_Date='2014-11-01') 
  join ImporterDistributor as ID on (Product_Delivery.Importer=ID.ID)
 where Product_Delivery.Transaction_Date='2014-11-01'
 group by concat(ImporterDistributor.name, '/', ID.name), Product_Delivery.Product

Below is the result

Distributor_Importer    OpeningPMS  OpeningAGO  OpeningATK
Aminata/Aminata         NULL        NULL        0
Aminata/Aminata         NULL        3500        NULL
Aminata/Aminata         29000       NULL        NULL
Gepco/West Oil          NULL        NULL        11802
Gepco/West Oil          NULL        33900       NULL
Gepco/West Oil          24500       NULL        NULL

This is the result I want to have:

Distributor_Importer    OpeningPMS  OpeningAGO  OpeningATK
Aminata/Aminata         29000       3500        0
Gepco/West Oil          24500       33900       11802

Can anyone help me out in achieving this?

mssql

Edited 9 Years Ago by Reverend Jim because: fixed code formatting

2 Contributors
10 Replies
200 Views
4 Days Discussion Span
Latest Post 9 Years Ago Latest Post by Trench37716

djjeavons 113 partis vestri scientia

9 Years Ago

Hi

Can you provide your table structures and an example of the data in each. It would make modifying your SQL statement a lot simpler and thus being able to help you a lot easier.

djjeavons 113 partis vestri scientia

9 Years Ago

Hi

There is no attachment. If you could list the field names and types I can guess the test data from your original post.

djjeavons 113 partis vestri scientia

9 Years Ago

Hi

You are using a join between ImporterDistributor and Product_Delivery which tells me that at least two tables are involved here.

Is the above the Product_Delivery or ImporterDistributor table? Please also provide the other table structure.

And I will probably need some example of the data.

Reply to this topic

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.

Trench37716 0 Newbie Poster · Answer 1 · 2015-03-05T10:01:46+00:00

This is coming from a single table. Please find attached my data structure and date itself.

Trench37716 0 Newbie Poster · Answer 2 · 2015-03-05T10:12:46+00:00

ID int Unchecked
Transaction_Date date Checked
Distributor int Checked
Importer int Checked
Opening int Checked
Quantity int Checked
Balance int Checked
Product int Checked
CommentPMS varchar(300) Checked
Date_Entered datetime Checked

This is the data structure.

Trench37716 0 Newbie Poster · Answer 3 · 2015-03-05T10:31:27+00:00

The actual data is coming from Product_Delivery. Only the Importers' names are coming from the IMporterDistributor's Table. The above data structure belongs to the Product_Delivery Table. The ImporterDistributor Table only have ID, Name in it.

djjeavons 113 partis vestri scientia · Answer 4 · 2015-03-05T11:42:46+00:00

Hi

I am not quite sure what was causing your current problem as I'm no expert in SQL, but I broke down the query into smaller parts using derived tables to get the desired output. This may not be the most efficient but I have tested it and it appears to work:

SELECT        DistributorImporter, SUM(OpeningPMS) AS OpeningPMS, SUM(OpeningAGO) AS OpeningAGO, SUM(OpeningATK) AS OpeningATK
FROM            (SELECT        Distributors.DistributorName + '/' + Importers.ImporterName AS DistributorImporter, 
                                                    CASE WHEN Product_Delivery_2. Product = 1 THEN Opening END AS OpeningPMS, 
                                                    CASE WHEN Product_Delivery_2. Product = 2 THEN Opening END AS OpeningAGO, 
                                                    CASE WHEN Product_Delivery_2. Product = 3 THEN Opening END AS OpeningATK
                          FROM            dbo.Product_Delivery AS Product_Delivery_2 INNER JOIN
                                                        (SELECT        DistributorID, DistributorName
                                                          FROM            (SELECT        ImporterDistributor_1.ID AS DistributorID, ImporterDistributor_1.Name AS DistributorName
                                                                                    FROM            dbo.ImporterDistributor AS ImporterDistributor_1 INNER JOIN
                                                                                                              dbo.Product_Delivery AS Product_Delivery_1 ON ImporterDistributor_1.ID = Product_Delivery_1.Distributor
                                                                                    GROUP BY ImporterDistributor_1.ID, ImporterDistributor_1.Name) AS derivedtbl_1) AS Distributors ON 
                                                    Product_Delivery_2.Distributor = Distributors.DistributorID INNER JOIN
                                                        (SELECT        dbo.ImporterDistributor.ID AS ImporterID, dbo.ImporterDistributor.Name AS ImporterName
                                                          FROM            dbo.ImporterDistributor INNER JOIN
                                                                                    dbo.Product_Delivery ON dbo.ImporterDistributor.ID = dbo.Product_Delivery.Importer
                                                          GROUP BY dbo.ImporterDistributor.ID, dbo.ImporterDistributor.Name) AS Importers ON Product_Delivery_2.Importer = Importers.ImporterID) 
                         AS TransactionData
GROUP BY DistributorImporter

HTH

Trench37716 0 Newbie Poster · Answer 5 · 2015-03-07T23:13:10+00:00

Thanks for the assistance. I want to add more columns. Is that possible?

djjeavons 113 partis vestri scientia · Answer 6 · 2015-03-09T09:21:56+00:00

Yes, it should be possible but it depends if that data can also be grouped so as to keep the same structure. What additional columns did you want to add?

Trench37716 0 Newbie Poster · Answer 7 · 2015-03-09T09:49:53+00:00

I want to add additional columns in the conditional part. I have already tried it out but the result is not what I want. The new columns I am added will be in the conditional part.

SELECT        DistributorImporter, max(OpeningPMS) AS OpeningPMS, max(OpeningAGO) AS OpeningAGO, max(OpeningATK) AS OpeningATK, 
sum(TransferPMS) as TransferPMS, sum(TransferAGO) as TransferAGO, sum(TransferATK) as TransferATK,
sum(QuantityPMS) as QuantityPMS, sum(QuantityAGO) as QuantityAGO, sum(QuantityATK) as QuantityATK,
min(BalancePMS) as BalancePMS, min(BalanceAGO) as BalanceAGO, min(BalanceATK) as BalanceATK, CommentPMS
FROM            (SELECT        Distributors.DistributorName + '/' + Importers.ImporterName AS DistributorImporter, 
                                                    CASE WHEN Product_Delivery_2. Product = 1 THEN Opening END AS OpeningPMS,
                                                    CASE WHEN Product_Delivery_2. Product = 2 THEN Opening END AS OpeningAGO, 
                                                    CASE WHEN Product_Delivery_2. Product = 3 THEN Opening END AS OpeningATK,
                                                    CASE WHEN Product_Delivery_2. Product = 1 THEN Transfer END AS TransferPMS, 
                                                    CASE WHEN Product_Delivery_2. Product = 2 THEN Transfer END AS TransferAGO,
                                                    CASE WHEN Product_Delivery_2. Product = 3 THEN Transfer END AS TransferATK,
                                                    CASE WHEN Product_Delivery_2. Product = 1 THEN Quantity end as QuantityPMS,
                                                    CASE WHEN Product_Delivery_2. Product = 2 THEN Quantity end as QuantityAGO,
                                                    CASE WHEN Product_Delivery_2. Product = 3 THEN Quantity end as QuantityATK,
                                                    CASE WHEN Product_Delivery_2. Product = 1 THEN Balance end as BalancePMS,
                                                    CASE WHEN Product_Delivery_2. Product = 2 THEN Balance end as BalanceAGO,
                                                    CASE WHEN Product_Delivery_2. Product = 3 THEN Balance end as BalanceATK, CommentPMS
                          FROM            dbo.Product_Delivery AS Product_Delivery_2 INNER JOIN
                                                        (SELECT        DistributorID, DistributorName
                                                          FROM            (SELECT        ImporterDistributor_1.ID AS DistributorID, ImporterDistributor_1.Name AS DistributorName
                                                                                    FROM            dbo.ImporterDistributor AS ImporterDistributor_1 INNER JOIN
                                                                                                              dbo.Product_Delivery AS Product_Delivery_1 ON ImporterDistributor_1.ID = Product_Delivery_1.Distributor
                                                                                                              where Product_Delivery_1.Transaction_Date=Cast('2014-11-01' as datetime2)
                                                                                    GROUP BY ImporterDistributor_1.ID, ImporterDistributor_1.Name) AS derivedtbl_1) AS Distributors ON 
                                                    Product_Delivery_2.Distributor = Distributors.DistributorID INNER JOIN
                                                        (SELECT        dbo.ImporterDistributor.ID AS ImporterID, dbo.ImporterDistributor.Name AS ImporterName
                                                          FROM            dbo.ImporterDistributor INNER JOIN
                                                                                    dbo.Product_Delivery ON dbo.ImporterDistributor.ID = dbo.Product_Delivery.Importer
                                                                                     where Product_Delivery.Transaction_Date=Cast('2014-11-01' as datetime2)
                                                          GROUP BY dbo.ImporterDistributor.ID, dbo.ImporterDistributor.Name) AS Importers ON Product_Delivery_2.Importer = Importers.ImporterID) 
                         AS TransactionData
GROUP BY DistributorImporter, TransactionData.TransferPMS, TransactionData.TransferAGO, TransactionData.TransferATK, TransactionData.CommentPMS