subquery???

Question

Post reply

subquery???

piresmdb

Grasshopper

Points: 23
More actions
September 30, 2014 at 7:28 am

#297047

Hi
I am trying to build a query where I want to extract the sum of the scores for each code MCC and get the percentage over the sum of all the scores over the last 90 days
select MCC, sum(score) as total from scores
(select Datediff(day, creationdate, getdate()) as Q from scores
where Datediff(day, creationdate, getdate()) <90)
group by MCC
TABLE
ID creationdate score MCC
1 2014-08-02 30 7422
. . . .
. . . .
. . . .

Viewing 5 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Alan Burstein SSC Guru Points: 61152 More actions · Answer 1

I'm thinking something like this...

WITH

scores(ID, creationdate, score, MCC) AS--SAMPLE DATA

(

SELECT 1, '2014-08-02', 30, 7422 UNION ALL

SELECT 2, '2014-08-03', 40, 7422 UNION ALL

SELECT 3, '2014-08-01', 100, 7500 UNION ALL

SELECT 4, '2014-08-02', 40, 7500 UNION ALL

SELECT 5, '2014-04-23', 50, 7500

),

last90 AS-- preagg...

(

SELECT last90 = sum(score)

FROM scores

WHERE creationdate >= cast(getdate()-90 as date)

AND creationdate <= getdate()+1

)

SELECTMCC,

MCC_total = sum(score),

PercentOfTotal = 1.0*sum(score)/last90

FROM scores

CROSS JOIN last90

WHERE creationdate >= cast(getdate()-90 as date)

AND creationdate <= getdate()+1

GROUP BY MCC,last90

"I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

-- Itzik Ben-Gan 2001

Luis Cazares SSC Guru Points: 183706 More actions · Answer 2

Basically the same thing as Alan. I just wanted to note that it's considered best practice to post DDL and sample data in a consumable format, as well as expected results. You're new to this site so it's not a big deal that you didn't do it this time, but try to do it in future occasions.

Note that we changed the way of calculating the dates to avoid using functions on columns and allow that indexes on that column can be used.

CREATE TABLE scores(

ID int,

creationdate date,

score int,

MCC char(5))

INSERT INTO scores VALUES

(1, '2014-08-02', 30, '7422'),

(1, '2014-08-05', 50, '7422'),

(1, '2014-07-02', 20, '7422'),

(1, '2014-07-01', 10, '7422')

select MCC, sum(score) as total , sum(score) / t.tot , t.tot

from scores ,

(select CAST( SUM(score) AS decimal( 18,8)) tot

from scores

where creationdate BETWEEN DATEADD( day, DATEDIFF( day, 0, getdate()), -90) --SARGable argument (functions don't use columns)

AND GETDATE()) t

group by MCC, tot

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

piresmdb Grasshopper Points: 23 More actions · Answer 3

Thank you very much

It is far more advanced than my current expertise but I going to try to understand it

piresmdb Grasshopper Points: 23 More actions · Answer 4

I have been trying to replicate it but this time without the where clause i.e. without date restriction and it is not working

The closest I got was this code but as I said it is not working

Any help please?

select

MCC,

sum(score) AS Total,

sum(score) / t.tot

from SCORES,

(

select CAST( SUM(score) AS decimal( 18,8)) AS tot

from scores

GROUP BY MCC

) AS t

group by

MCC,

t.tot?