I have the following tables: - agencies: | code | - commissions: | registered_at | action | origin | destination | commission | I need to SUM(commissions.commission) for the following conditions - agencies.code = commissions.origin HAVING commissions.action = "collection" - agencies.code = commissions.destination HAVING commissions.action = "delivery" ``` WHERE Date(commissions.registered_at) between '2023-01-01 00:00:00' and '2023-01-31 23:59:59' Group By agencies.code ``` As you can see, these 2 tables have no PK / FK relations. They are related by the destination / origin and the action being queried. I tried to make a double Left Join on commissions, but that seems to call the second join for the number of records from the first join. I also don't see how I can make it work with IF conditions inside SELECT, as the conditions to join the tables are different. Any help is appreciated.

I have 2 tables - product id designation 1 coca 2 Pepsi 3 Fanta 4 Seven 5 Eight 2)sub_product product_id name quantity 1 sm 10 1 lg 10 1 xl 20 2 1L 10 2 2L 20 2 5L 20 3 Ty 10 3 Sa 20 4 ha 20 4 kd 30 what I wanna have is this: the designation from the product table and total quantity which represent the SUM of the quantity that have the same product_id designation total quantity Coca 40 Pepsi 50 Fanta 30 Seven 50 Notes : I use SQL server

In the book Getting Started with SQL, Thomas Nield talks about a technique he calls the zero/null case trick: There is a simple but powerful tool that can apply different filtering conditions to different aggregations. We can create separate total counts when a tornado was present versus not present in two separate columns: ``` SELECT year, month, SUM(CASE WHEN tornado = 1 THEN precipitation ELSE 0 END) as tornado_precipitation, SUM(CASE WHEN tornado = 0 THEN precipitation ELSE 0 END) as non_tornado_precipitation FROM station_data WHERE year >= 1990 GROUP BY year, month ``` What we have effectively done is get rid of the WHERE conditions when tornado = 1 or tornado = 0, and then move those conditions to CASE expressions inside the SUM() functions. If the condition is met, the precipitation value is added to the sum. If not, a 0 is added, having no effect. We do these for both columns, on for when a tornado was present and one for when a tornado was not present respectively. You can make the CASE expression have as many condition/value pairs as you need, giving you the ability to do highly particular interceptions of values with your aggregations. You can also use this trick to emulate crosstabs and pivot tables, expressing aggregations into separate columns rather than in rows. A common example of this is doing current year/previous year analysis, as you can express separate years with different columns. As a novice, that technique seems like it could come in really handy for summarizing data. I want to look up that technique online to get more information. The author of that book calls the technique the "zero/null case trick". But when I google that term, I don't get many results. Question: Is there a generally accepted name for that technique? (that would yield more results when searching online)

In our application, we have a query which on an API endpoint to return users who have 'activity' to a named client or matter (one or many - specified by ID). This query takes about 15 seconds to return when the activity table has 30 million rows (note there is also ~600k rows in the 'Asset' table and 2700 'users'). The rough schema for the tables can be found at the bottom of my question. Below is our query to find 'users who have activity over a threshold'. For brevity I have placed the view definition below also. As this endpoint returns a page of data, a second, similar, query is also ran to get the total count of elements to populate paging response - essentially then the performance of the endpoint is 2x the query performance. My question is essentially, what techniques should I be applying to get improve the performance of this query? A 'benchmark' we try to stick to is 'subsecond' on our endpoint responses. Query plan can be found here. ``` SELECT DISTINCT t.type, t.sid, t.name, t.emailAddress, t.jobTitle FROM sec.Trustee t INNER JOIN ( SELECT data.sid, SUM(data.hoursBilled) as hoursBilled, SUM(data.docsAccessed) as docsAccessed, data.asset_type as asset_type, data.displayId as displayId, data.displayName as displayName FROM ( SELECT billing.trustee_sid as sid, 0 as hoursBilled, billing.recordedValue as docsAccessed, a.type as asset_type, a.displayId, a.displayName FROM sec._DocumentsBilling billing INNER JOIN sec.SessionSid s ON s.sid = billing.client_sid AND s.setID = @P0 INNER JOIN sec.Asset a ON a.sid = billing.client_sid AND billing.recordedDate > @P1 AND billing.client_sid IN (@P2) ) data GROUP BY data.sid, data.asset_type, data.displayId, data.displayName HAVING SUM(data.docsAccessed) > @P4 ) trusteeData ON trusteeData.sid = t.sid ORDER BY sid OFFSET @P6 ROWS FETCH NEXT @P7 ROWS ONLY ``` The

i need to collect some values from multiple tables and sum this values as a column to the result table. my query looks like this: ``` SELECT u.ID as id, ( SELECT `meta_value` as user_name FROM `wxe4_usermeta` WHERE `umeta_id` = u.ID AND `meta_key` = 'nickname' ) as user_name, ( SELECT SUM(rounds) FROM wxe4_170 WHERE user = u.ID ) as a170_score, ( select IF (count(*) > 0, count(*)*66, 0) FROM wxe4_aroundtheworld WHERE user = u.ID ) as atw_score, ( select IF (count(*) > 0, count(*)*100, 0) FROM wxe4_X100 WHERE user = u.ID ) as x100_score, SUM(a170_score + atw_score + x100_score) as darts_total_thrown FROM darts.wxe4_users as u ``` the sum throws a "Error Code: 1054. Unknown column 'a170_score' in 'field list'" Error. what i get without sum: ``` | id | user_name | a170_score | atw_score | x100_score | -------------------------------------------------------- | 1 | someUser | 449 | 3102 | 200 | ``` what i expect with sum: ``` | id | user_name | a170_score | atw_score | x100_score | darts_total_thrown | ---------------------------------------------------------------------------- | 1 | someUser | 449 | 3102 | 200 | 3751 | ``` Why cant i access these values and how to resolve this?

I have a table ``` +-------+----------+------------+----------+---------+ | Plant | LineName | WorkCenter | Material | ProdQty | +-------+----------+------------+----------+---------+ | x | xl | xl1 | y1 | 1 | | x | xl | xl2 | y1 | 1 | | x | xl | xl3 | y1 | 1 | | x | xl | xl1 | y2 | 1 | | x | xl | xl2 | y2 | 1 | | x | xl | xl3 | y2 | 1 | +-------+----------+------------+----------+---------+ ``` I am trying to count the number of different materials, and sum the quantities along the LineName, so that I get something like this: ``` +----------+------+---------+ | LineName | Cmat | ProdQty | +----------+------+---------+ | xl | 2 | 2 | +----------+------+---------+ ``` Instead, when I use a combination of count distinct, sum and group by, I get an incorrect result: ``` +----------+------+---------+ | LineName | Cmat | ProdQty | +----------+------+---------+ | xl | 2 | 6 | +----------+------+---------+ ``` How should I do this correctly? I've tried with ``` SELECT LineName, COUNT(DISTINCT(Material) as Cmat, SUM(ProdQty) as ProdQty FROM table GROUP BY LineName ``` But it does not create the desired result. I've been looking on stack exchange for some similar topics, but they don't seem to match my question.

I ran bellowing query; Query: ``` SELECT j.jobId, j.productId, p.productUnitPrice FROM JobRequiredProducts J JOIN Product p ON p.productId = j.productId WHERE j.jobId = 1 ORDER BY j.jobId ``` And got bellowing result; Query result: ``` jobId | productId | unitPrice _____________________________ 1 | 4 | 175.99 1 | 5 | 100.00 1 | 6 | 125.00 ``` Now, I want to calculate all unitPrice to get the total. the result I expect is; Expecting result: ``` jobId | productId | Total ____________________________ 1 | 4 | 175.99 1 | 5 | 100.00 1 | 6 | 125.00 null | null | 400.99 ``` Therefor bellowing is the query I tried; Query I tried: ``` SELECT j.jobId, j.productId, SUM(p.productUnitPrice) AS 'Total' FROM JobRequiredProducts J JOIN Product p ON p.productId = j.productId WHERE j.jobId = 1 GROUP BY j.jobId,j.productId WITH ROLLUP ``` But it didn't give me the result I expected, where I went wrong and what I gotta do to get the result I expect?

I have been looking online for an answer but do not really know how to formulate correctly what I would like to achieve and whether it's possible, sorry if the question sounds dumb. I am using Postgresql. I have price data per day. ``` CREATE TEMP TABLE Price (id,Day, Price) AS VALUES (1, 1, 40), (2, 1, 20), (3, 1, 50), (4, 1, 10), (5, 1, 20), (6, 1, 60), (7, 2, 10), (8, 2, 40), (9, 2, 10), (10,2, 20), (11,2, 10); ``` I want to assign numbers (1, 2, 3...) to the price data based on the day and the sum of the prices. Every time when the sum > 60, the sum calculation starts again + every time when a new day is reached, the sum calculation starts again. So for example: Row 1 [day 1, price 40] = 1. Then for row 2 [day 1, price 20] the price sum is 20 + 40 60, therefore the counting of the sum has to restart and number 2 is assigned to row 3. The result will look like: Does anyone know if this is achievable and how? I understand how to take the `SUM(Price) OVER (PARTITION BY Day ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)`, but do not know how to restart the sum counting when the condition is met. Thank you in advance for the help!

I have the below Query and I want the Ageing profile to show as a new column my ageing profile is 0-30, 31-60, 61-90, 91-120, 121-180, 181-365, 365+ I basically want the results to appear lie this with the Net due appearing in the appropriate ageing bracket. ``` BU 0-30 31-60 61-90 91-120 121-180 181-365 365+ -------------------------------------------------------------- A B C ``` Query: ``` select BU, Ageing, sum(NetDue) as Netdue from [dbo].[vw_FACT_CONSOL_CREDITORS] where date = @date Group by BU, Ageing Order by BU ; ``` I am new to SQL and just trying to learn so apologise as this is basic stuff Thanks

I'm having difficulty with `GROUP BY` in MySQL. My database setup: ``` client_visit - id - member_id - status_type_id (type_of_visit table) - visit_starts_at - visit_ends_at member - id schedule_event - id - member_id - starts_at - ends_at type_of_visit - id - type (TYPE_BOOKED, TYPE_PRESENT etc) ``` For the purpose of this question: a `member` teaches a class or leads an activity (a `schedule_event`) at a given time. A `client` signs up for this class or activity. For example: Client A, B and C book visits and those go to `client_visit` table which consists of `schedule_event_id` and `member_id`, so we know what class and what member is teaching/or having activity. Now, we want to know the total time a given member spent teaching/leading events that clients signed up for (based on the `client_visit` `type_of_visit` column equivalent to "Booked" or "Present"). We'll take member ID 82 as our test case. Member ID 82 had 4 clients in two different classes, so if each class took 2h 15 minutes (8100 seconds), that means total time should be 16200 seconds. Here's my query first: ``` SELECT cv.member_id AS `member_id`, sch.id AS `scheduleId`, cv.visit_starts_at AS `visitStartsAt`, TIMESTAMPDIFF(SECOND, sch.starts_at, sch.ends_at) AS `totalTime` FROM `schedule_event` AS `sch` LEFT JOIN `client_visit` AS `cv` ON cv.schedule_event_id = sch.id INNER JOIN `type_of_visit` AS `tov` ON tov.id = cv.status_type_id WHERE (tov.type = 'TYPE_BOOKED' OR tov.type = 'TYPE_PRESENT') and cv.member_id = 82 ``` The result is as follows: This shows me the clients for the first class, and the one for the second. I just want two rows, one for each class. So, I add this: ``` GROUP BY sch.id ``` Now, the result is as follows: So far so good, I know that there are two schedule ids for this member, so I modified the group by to pull those together into one: ``` GROUP BY sch.id AND cv.member_i