Select query to get groupwise data result in mysql ?

Question

Hi , can anyone knows suggest with example .
Tried Query : select * from da5.SwitchPrice as S inner join da5.SwitchPriceGroup as G on S.PSGID=G.PSGID;

Result we got :
Barcode SwitchPrice PSGID PSGroupName
______________________________________________

A01 Price2 1 LUNCH
A02 Price3 1 LUNCH
A03 Price4 1 LUNCH
A04 Price3 2 EVENING

Result we desire to get :

Barcode SwitchPrice PSGID
________________________________________________

PSGROUPName :LUNCH
A01 Price2 1
A02 Price3 1
A03 Price4 1

PSGROUPName:EVENING
A04 Price3 2

Thanks in advance

Tuhin Paul · Answer

If you prefer to implement this in C# , here's how you can do it:

using System;
using System.Collections.Generic;
using System.Linq;

class Program
{
    static void Main()
    {
        // Simulated Data (Replace this with actual database query results)
        var data = new List<(string Barcode, string SwitchPrice, int PSGID, string PSGroupName)>
        {
            ("A01", "Price2", 1, "LUNCH"),
            ("A02", "Price3", 1, "LUNCH"),
            ("A03", "Price4", 1, "LUNCH"),
            ("A04", "Price3", 2, "EVENING")
        };

        // Group by PSGroupName
        var groupedData = data.GroupBy(d => d.PSGroupName);

        // Print the Desired Output Format
        foreach (var group in groupedData)
        {
            Console.WriteLine($"PSGROUPName: {group.Key}");
            foreach (var item in group)
            {
                Console.WriteLine($"{item.Barcode,-8} {item.SwitchPrice,-10} {item.PSGID}");
            }
            Console.WriteLine(); // Add a blank line between groups
        }
    }
}

PSGROUPName: LUNCH
A01      Price2     1
A02      Price3     1
A03      Price4     1

PSGROUPName: EVENING
A04      Price3     2

SQL Query :
- Your SQL query is correct and fetches all the necessary data. The formatting issue cannot be resolved purely in SQL.
Post-Processing :
- Use a programming language like Python or C# to process and format the data after fetching it from the database.
Scalability :
- This approach works well for small to medium datasets. For very large datasets, consider optimizing the grouping logic or processing the data in chunks.

Tuhin Paul · Answer

SQL Query Adjustment If you want to fetch the data in a single query, your existing SQL query is already correct: SELECT S.Barcode, S.SwitchPrice, S.PSGID, G.PSGroupName FROM da5.SwitchPrice AS S INNER JOIN da5.SwitchPriceGroup AS G ON S.PSGID = G.PSGID; You can use this query to fetch the data and then process it programmatically

Tuhin Paul · Answer

To achieve the desired output where the PSGROUPName acts as a header and the corresponding rows are grouped under it, you need to process the query result programmatically. SQL alone does not support this kind of hierarchical or formatted output directly. However, you can achieve this by using a programming language like Python, C#, or even SQL with some post-processing logic.

Below is an example implementation in Python using pandas to format the data as required:

import pandas as pd

# Sample Data (Simulating your SQL Query Result)
data = {
    "Barcode": ["A01", "A02", "A03", "A04"],
    "SwitchPrice": ["Price2", "Price3", "Price4", "Price3"],
    "PSGID": [1, 1, 1, 2],
    "PSGroupName": ["LUNCH", "LUNCH", "LUNCH", "EVENING"]
}

# Create a DataFrame
df = pd.DataFrame(data)

# Group by PSGroupName
grouped = df.groupby("PSGroupName")

# Print the Desired Output Format
for group_name, group_data in grouped:
    print(f"PSGROUPName: {group_name}")
    # Select only the required columns for display
    group_data = group_data[["Barcode", "SwitchPrice", "PSGID"]]
    print(group_data.to_string(index=False))
    print()  # Add a blank line between groups

PSGROUPName: LUNCH
 Barcode SwitchPrice  PSGID
 A01        Price2      1
 A02        Price3      1
 A03        Price4      1

PSGROUPName: EVENING
 Barcode SwitchPrice  PSGID
 A04        Price3      2

Input Data :
- The input data is simulated using a dictionary and converted into a pandas.DataFrame. In a real-world scenario, you would fetch this data from your database using a library like sqlalchemy or pyodbc.
Grouping :
- The groupby function groups the data by the PSGroupName column.
Formatting :
- For each group, the group name (PSGroupName) is printed as a header.
- The corresponding rows are displayed below the header, showing only the required columns (Barcode, SwitchPrice, PSGID).
Blank Line :
- A blank line is added between groups for better readability.

Karthik K · Answer

@ Daniel Wright, Thanks for your response ., we require Groupname as header , details in the 2nd row like that Thanks

Daniel Wright · Answer

Based on the provided scenario, it seems like you are looking for a way to group the results based on the "PSGroupName" from your query. To achieve the desired result, you can utilize the `GROUP BY` clause in MySQL. Here is an example query that should help you achieve the desired output:


SELECT 
    S.Barcode, 
    S.SwitchPrice, 
    S.PSGID,
    G.PSGroupName
FROM 
    da5.SwitchPrice as S 
INNER JOIN 
    da5.SwitchPriceGroup as G 
ON 
    S.PSGID = G.PSGID
GROUP BY 
    G.PSGroupName,
    S.Barcode,
    S.SwitchPrice,
    S.PSGID;

In this query, we have included the `GROUP BY` clause to group the results based on the "PSGroupName". By including all the selected columns along with the grouping column "PSGroupName", you can get the desired output where the results are grouped under each "PSGroupName".

This query should help you achieve the result structure you desire, with the data grouped under the respective "PSGroupName". If you have any further questions or need additional clarification, feel free to ask.

Select query to get groupwise data result in mysql ?

Tuhin Paul

Tuhin Paul

Tuhin Paul

Karthik K

Daniel Wright