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 { ("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.