Skip to content

Tag: sql-server

Sum rows with same Id based on type and exlude where SUM = 0

I have this table MOVEMENTS: I’m trying to get all the FatherId with the SUM of IN – OUT Movments > 0. So the result would be: FatherId = B not showing because SUM(MovementType = IN) – SUM (MovementType = OUT) = 0 I tried with That gives me the result grouped by FatherId, but I’m no…

Combining a recursive CTE with another query

I have a table of locations each of which can have a parent location I managed to create a recursive CTE which gives me parent location id (plus the original location id) for any given location id e.g. where LocationId = 3 would return: In another table I have a query which will return LocationId as one of th…

Use average of values in “where” clause

I want to select the top x value from a query where the average of valid values for a particular field is above 10. Why is this throwing an error? Answer Your code is throwing an error because avg() is an aggregation function and cannot be in the where clause. What you want seem to want is apply: The subquery

Remove text after a codeword

I have a list of company names that have been sent with their added company numbers. How can I remove these company numbers (of different length) in SQL? “Limited” if in the name needs to be …

Query help on sales transaction table

I have a table that contains transaction level sales data. I am trying to satisfy a reporting request as efficiently as possible which I don’t think I am succeeding at right now. Here is some test …