Querying data with different conditions on multiple columns

Question

I have a table with date column and 3 different other columns: X, Y, Z.
I want the get the date, X and Y for last month and Z column the sum for all the previous dates.

This is the query that I worked on:

SELECT date,
  x,
  y,
  (
    SELECT z
    FROM Table
    WHERE date < DATEADD(DAY, -(DAY(GETDATE())), GETDATE())
  )
FROM Table
WHERE date BETWEEN DATEADD(mm, DATEDIFF(mm, 0, GETDATE()) - 1, 0) AND
  DATEADD(DAY, -(DAY(GETDATE())), GETDATE());

Here's some sample data:

And here's how I want the the result:

date       | X | Y | Z  
-----------+---+---+---  
2019-08-01 | 8 | 8 | 14 
2019-08-02 | 2 | 2 | 14

The date, X and Y only for last month and Z the sum of all time (1+1+2+8+2=14).

Gordon Linoff · Accepted Answer · 2019-09-16T21:38:13.813

2

Here is one way to do what you want:

select t.date, t.x, t.y, t.new_z
from (select t.*, sum(z) over () as new_z,
             rank() over (order by year(date) desc, month(date) desc) as seqnum
      from t
     ) t
where seqnum = 1;

Actually, you could also do:

select top (1) with ties t.date, t.x, t.y, sum(z) over () as z
from t
order by year(date) desc, month(date) desc;

edited Sep 16 '19 at 21:38

answered Sep 16 '19 at 20:51

Gordon Linoff

1,242,037
58
646
786

the sub query returns more then one value and that one gets just one – chawky Sep 16 '19 at 21:35
@chawky . . . Your original question seemed quite clear that you wanted one row. But I fixed the answer to get rows from the most recent month. – Gordon Linoff Sep 16 '19 at 21:38
it worked but can you explain what you did there ? – chawky Sep 16 '19 at 21:46
1

how did it get only the date for last month ? i dont see a condition for that , and what does with ties do ? – chawky Sep 16 '19 at 21:48
@chawky . . . https://learn.microsoft.com/en-us/sql/t-sql/queries/top-transact-sql?view=sql-server-2017. – Gordon Linoff Sep 17 '19 at 01:54

computercarguy · Answer 2 · 2019-09-16T22:26:38.013

You are on the right track, but you're missing a few things.

SELECT max(OrderDate), CustomerID, EmployeeID, ( select sum(ShipperID) from [Orders] WHERE OrderDate <= '1996-07-15' ) FROM [Orders] WHERE OrderDate <= '1996-07-15';

SELECT OrderDate, CustomerID, EmployeeID, ( select sum(ShipperID) from [Orders] inside WHERE inside.OrderDate <= outside.OrderDate ) FROM [Orders] outside WHERE OrderDate between '1996-07-11' and '1996-07-15';

WITH SumZ as (select sum(ShipperID) as zColumn from [Orders] inside
    WHERE inside.OrderDate <= '1996-07-15')

SELECT OrderDate, CustomerID, EmployeeID, SumZ.zColumn
FROM [Orders] outside, SumZ
WHERE OrderDate between '1996-07-11' and '1996-07-15';

I used a different dataset, but to the same effect.*

https://www.w3schools.com/sql/trysql.asp?filename=trysql_op_in

Since you want all the z columns to be the same, using a WITH (actually called a "common table expression" or CTE) prevents your query from running the same sub-query multiple times. Since there's nothing to JOIN with, I didn't do one, but you can JOIN on a CTE just like any other table, view, etc.

Further readin on CTEs:
https://www.essentialsql.com/introduction-common-table-expressions-ctes/
and
When to use Common Table Expression (CTE)

It queries the Orders table based on the "largest" Date, gets the data from 3 columns of that row, and sums the values of a 4th column based on the same criteria as the outer query.

*In my example, the data is just there for an example and doesn't mean anything useful. ~~Also, I simplified the date selection, since the data I used didn't actually include a Date column.~~ Turns out, it is a Date column.

it retuns one result , mine can have mutliple results , just in the example i gave there is just ons line with the date of last month , if there are a lot of lines like that it will have multiple results — chawky, Sep 16 '19 at 21:22
@chawky, is the last column still supposed to be 14 for both results or is it supposed to be 16 for one? Or is it now supposed to be 16 for both? — computercarguy, Sep 16 '19 at 21:43
the last column is the sum of z for all the dates so 14 for both — chawky, Sep 16 '19 at 22:10
@chawky, but 14 is no longer the sum of `z` for all the dates. — computercarguy, Sep 16 '19 at 22:12
@chawky, sorry, I guess I read/remembered that incorrectly. I should have done the math again. I'm no longer sure why I thought 14 was wrong. The math you have on the answer is outdated, though, since that's still saying 12. — computercarguy, Sep 16 '19 at 22:45

Neeraj Agarwal · Answer 3 · 2019-09-16T22:00:57.563

1

This checks for last month. Updated for new sample data.

select
(select t2.x from sample t2 where (month(t2.date) = month(getdate()) - 1) and year(t2.date) = year(getdate())
and t2.date = (select max(t3.date) from sample t3)) as x,
(select t2.y from sample t2 where (month(t2.date) = month(getdate()) - 1) and year(t2.date) = year(getdate())
and t2.date = (select max(t3.date) from sample t3)) as y,
    sum(t1.z) as z
from sample t1

edited Sep 16 '19 at 22:00

answered Sep 16 '19 at 21:25

Neeraj Agarwal

1,059
6
5

i got this error : Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression. – chawky Sep 16 '19 at 21:34
@chawky I updated the query for the updated sample data. – Neeraj Agarwal Sep 16 '19 at 22:04

Querying data with different conditions on multiple columns

3 Answers3