i have question about query result group. The image is a example. it is cable list. Each cable come with two attributes, ‘From’ location and ‘To’ location. if we’d like to group the cable list by …
Tag: group-by
Group by and display count in Excel
I am trying to learn some Excel’s features and I want to know if it is possible to make an SQL-like statement in Excel. For example, I distincted a dataset and the output is: I want to make a count(*) and a group by like: Answer Initial data: Create a pivot table. When you drag columns like this… …
Retrieving the distinct count across multiple columns
Say I have the following table in MS SQL Server: Make Model Year Honda Accord 1997 Honda Accord 1997 Honda Accord 1997 Honda Civic 2001 Honda Civic 2005 Honda Civic 2005 Toyota Corolla 2010 Nissan Pathfinder 1997 I want to be able to retrieve the count for the unique combination and get the results like the f…
How to do an arithmetic operation with aliased column in SQL
I have a databse table as like below: Here, received_by and sent_by are two user ID those who are receiving and sending the product respectively. I want to calculate the total amount of each product of a single user by subtracting the sent amount from received amount. My current query looks like below: Here I…
Cohort Analysis using SQL (Snowflake)
I am doing a cohort analysis using the table TRANSACTIONS. Below is the table schema, Below is a quick query to see how USER_ID 12345 (an example) goes through the different cohorts based on the date filter provided, The result for this query with the time frame (two weeks) would be and this USER_ID would be …
SQL union grouped by rows
Assume I have a table like this: col1 col2 col3 col4 commonrow one two null commonrow null null three How to produce a result to look like this: col1 col2 col3 col4 commonrow one two three Thanks Answer like this, you can group by col1 and get the maximum in each group:
How to carry over latest observed record when grouping by on SQL?
(I’ve created a similar question before, but I messed it up beyond repair. Hopefully, I can express myself better this time.) I have a table containing records that change through time, each row representing a modification in Stage and Amount. I need to group these records by Day and Stage, summing up t…
How do I restrict my SQL aggregation Rollups to a specific grouping?
The following SQL returns 5 grouped columns and two aggregated columns: I do want the rollup to the level of TenancyName, but it don’t need all the other intermediate rollup lines. In fact, you can see that rolling up from the Doctor’s (Employee’s) row up to the EmployeeId produces the exact…
SQL procedure group by parameter value
I want to use group by functionality with dynamic value. Ex: When I code like this I get the error == > Each GROUP BY expression must contain at least one column that is not an outer reference. Any workaround for this? Answer You need to be very careful with this type of code as it is prone to SQL
About “group function is not allowed here”
I have a table with job, salary and date columns. I am writing the following query in PL/SQL, but I am getting an error group function is not allowed here How can I solve this problem? Answer Your query makes no sense (to me, at least). What does that average salary represent? Whose average salary? Here’…