Hello I am very new to SQL and I have an issue. I have 3 tables job, revenue and cost. job.unid = cost.job_unid and revenue.job_unid I want to find the total revenue and cost for each job and then find the profit for each job all on one row: The headers looking something like this: Job No, Total Revenue, Tota…
Tag: aggregate-functions
How to find group which is having Status Active? [closed]
Closed. This question needs details or clarity. It is not currently accepting answers. Want to improve this question? Add details and clarify the problem by editing this post. Closed 4 years ago. Improve this question I have a id which is having more than one Accountid in a table with status Active . I need t…
Sybase ASE 15 Aggregate Function for strings
I’am findind a way to aggregate strings from differents rows into a single row in sybase ASE 15. Like this: Something like FOR XML PATH in T-SQL. Thanks! Answer Sybase ASE does not have any string aggregate functions like list() or group_concat(); and while there is some support for FOR XML, it does not…
Union of arrays as aggregate function
I have the following input: I want the following output: I am grouping by name. For each group, the count should be aggregated as the max and the options should be aggregated as the union. I am having troubles figuring out how do the the latter. Currently, I have this query: http://rextester.com/YTZ45626 I kn…
Writing my own aggregate function in postgresql
I’ve never wriiten my own aggreagtes, only store procedures and I need some advice. I want to write a custom aggregate which is going to return maximum value of the integer rows and incerement it by 10. How can I do that? I tried this: but it didn’t work. Can someone help me out? I got the error: …
How to calculate MAX(SUM()) in postgresql in particular to query below
I am trying to get MAX(SUM()) in PostgreSQL, which is not possible directly in PostgreSQL. I am trying to get the result by any method, but unable to get the desired result. My table is like this – …
Generate_series in Postgres from start and end date in a table
I have been trying to generate a series of dates (YYYY-MM-DD HH) from the first until the last date in a timestamp field. I’ve got the generate_series() I need, however running into an issue when trying to grab the start and end dates from a table. I have the following to give a rough idea: Postgres 9.3…
PostgreSQL distinct rows joined with a count of distinct values in one column
I’m using PostgreSQL 9.4, and I have a table with 13 million rows and with data roughly as follows: There are indices on md5(a), on b, and on (md5(a), b). (In reality, a may contain values longer than 4k chars.) There is also a primary key column of type SERIAL which I have omitted above. I’m tryi…
Aggregate columns with additional (distinct) filters
This code works as expected, but I it’s long and creepy. As you can see, it consists of 3 repetitive parts for retrieving: player name and the amount of games they played player name and the amount of games they won player name and the amount of games they lost And each of those also consists of 2 parts…
Calculate Median with SQL (DB2)
I’m having issues calculating the median for my result set and could use some help. I need to provide the median, max, min, avg, and standard deviation. There are 222 rows which can be more or less and I’m not sure what I have so far is an accurate way of calculating the median. Here is my query. …