how to select Query to show Groupwise data ?

Question

Hi All ,

we require to have query to show the data with groupwise data in the query. can anyone suggest with sample query .

MYQUUERY: select * from da5.SwitchPrice as S inner join da5.SwitchPriceGroup as G on S.PSGID=G.PSGID;

Result we got :
Barcode SwitchPrice PSGID PSGroup
______________________________________________
A01 Price2 1 LUNCH
A02 Price3 1 LUNCH
A03 Price4 1 LUNCH
A04 Price3 2 EVENING

Result we desire to get :

Barcode SwitchPrice PSGID
________________________________________________
PSGROUP :LUNCH
A01 Price2 1
A02 Price3 1
A03 Price4 1
PSGROUP :EVENING
A04 Price3 2

Thanks in advance

Tuhin Paul · Answer

If you need the exact hierarchical structure (with group names as headers), it's better to handle this in your application code. import pandas as pd # Simulated Data (Replace this with actual database query results) data = { "Barcode": ["A01", "A02", "A03", "A04"], "SwitchPrice": ["Price2", "Price3", "Price4", "Price3"], "PSGID": [1, 1, 1, 2], "PSGroup": ["LUNCH", "LUNCH", "LUNCH", "EVENING"] } # Create a DataFrame df = pd.DataFrame(data) # Group by PSGroup grouped = df.groupby("PSGroup") # Print the Desired Output Format for group_name, group_data in grouped: print(f"PSGROUP :{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 PSGROUP :LUNCH Barcode SwitchPrice PSGID A01 Price2 1 A02 Price3 1 A03 Price4 1 PSGROUP :EVENING Barcode SwitchPrice PSGID A04 Price3 2

Tuhin Paul · Answer

The query will produce the following result: Header Barcode SwitchPrice PSGID PSGROUP :LUNCH A01 Price2 1 A02 Price3 1 A03 Price4 1 PSGROUP :EVENING A04 Price3 2 For each group ( PSGroup ), the first row will have the group name in the Header column. Subsequent rows in the same group will have an empty Header column. The ORDER BY clause ensures that the rows are grouped and sorted correctly. Limitations: Formatting : This approach simulates the desired output but does not fully replicate the hierarchical structure. The Header column contains the group name, and subsequent rows leave it blank. Application-Level Formatting : For a cleaner and more flexible solution, consider processing the query results in your application code (e.g., Python, C#) to format the output exactly as needed.

Tuhin Paul · Answer

To achieve the desired output where the PSGROUP name acts as a header followed by its corresponding rows, SQL alone cannot directly produce hierarchical or formatted outputs like this. However, you can simulate this behavior using SQL by introducing a "header row" for each group. SELECT CASE WHEN ROW_NUMBER() OVER (PARTITION BY G.PSGroup ORDER BY S.Barcode) = 1 THEN CONCAT('PSGROUP :', G.PSGroup) ELSE '' END AS Header, S.Barcode, S.SwitchPrice, S.PSGID FROM da5.SwitchPrice AS S INNER JOIN da5.SwitchPriceGroup AS G ON S.PSGID = G.PSGID ORDER BY G.PSGroup, S.Barcode; ROW_NUMBER() Function : The ROW_NUMBER() function assigns a unique number to each row within a group ( PARTITION BY G.PSGroup ) based on the specified order ( ORDER BY S.Barcode ). For the first row in each group ( ROW_NUMBER() = 1 ), we display the group name ( PSGROUP : ). Otherwise, we leave the Header column blank. CASE Statement : The CASE statement checks if the current row is the first row in its group. If it is, it displays the group name ( PSGROUP : ). Otherwise, it leaves the Header column empty. Concatenation : The CONCAT function is used to prepend PSGROUP : to the group name. Ordering : The ORDER BY G.PSGroup, S.Barcode ensures that rows are grouped by PSGroup and sorted by Barcode .

Eliana Blake · Answer

To achieve the desired result of grouping data based on the 'PSGroup' column in your SQL query, you can utilize the `GROUP BY` clause along with appropriate sorting. Here's a modified version of your query that should produce the desired outcome: SELECT 'PSGROUP :' || G.PSGroup AS PSGROUP, S.Barcode, S.SwitchPrice, S.PSGID FROM da5.SwitchPrice AS S INNER JOIN da5.SwitchPriceGroup AS G ON S.PSGID=G.PSGID ORDER BY G.PSGroup, S.Barcode; In this modified query: - We concatenate the string `'PSGROUP :'` with the value of `G.PSGroup` to represent the group name in the result set. - The `GROUP BY` clause has been replaced with the `ORDER BY` clause to organize the results based on the 'PSGroup' first and then 'Barcode'. - The result set will display the 'PSGROUP' followed by the associated 'Barcode', 'SwitchPrice', and 'PSGID' columns. By executing this query, you should obtain the desired grouping of data based on the 'PSGroup' in your output, similar to the format you provided in your example. If you have any further questions or need clarification, feel free to ask!