complicated query (where statement inside select statement, don'tknow how to explain it)

[Joe C.]

hello, just joined the group, i've run into a wall and am seeking some
help.

here is my query:

SELECT
DATEPART(year, TTmain.TTDate) AS 'Year',
SUM(TTmain.TTAm t) + SUM(TTmain.TTFe e) AS 'Total'
FROM
TTmain INNER JOIN TTdealers ON
TTmain.dealerID = TTdealers.deale rID
GROUP BY
DATEPART(year, TTmain.TTDate)

basically, it's summing the values of a column, then grouping by year,
results look like this:

Year | Total
----------------------------
2007 | 194309.9
2008 | 418763.24

i need to include another column, which is a summation of the "Total"
value (SUM(nsfAmt) + SUM(nsfFee) AS 'Total') but filtered based on
customer type.

so, suppose the query would look something like this (this is not
correct, it's for illustration only):

SELECT
DATEPART(year, TTmain.TTDate) AS 'Year',
SUM(TTmain.TTAm t) + SUM(TTmain.TTFe e) AS 'Total',
SUM(TTmain.TTAm t) + SUM(TTmain.TTFe e) AS 'Different Total' where
TTdealers.deale rID = 1
FROM
TTmain INNER JOIN TTdealers ON
TTmain.dealerID = TTdealers.deale rID
GROUP BY
DATEPART(year, TTmain.TTDate)

so, the "Total" result is without any conditions, and the "Different
Total" is with a condition of TTdealers.deale rID = 1. can the sum of
both values be included on the same row of the result?

i'm not sure if this possible (or if the question i'm posing even
makes sense), but thought to bounce it off the pros on this board.

any help would be greatly appreciated.

[Plamen Ratchev]

You can use the CASE function to filter the total for the selected customer:

SELECT
DATEPART(year, TTmain.TTDate) AS 'Year',
SUM(TTmain.TTAm t + TTmain.TTFee) AS 'Total',
SUM(CASE WHEN TTdealers.deale rID = 1
THEN TTmain.TTAmt + TTmain.TTFee
ELSE 0
END) AS 'Total Dealer 1'
FROM
TTmain INNER JOIN TTdealers ON
TTmain.dealerID = TTdealers.deale rID
GROUP BY
DATEPART(year, TTmain.TTDate)

HTH,

Plamen Ratchev
http://www.SQLStudio.com

[Joe C.]

my goodness, how awesome is this right here? IT WORKS PERFECTLY!

thank you SO MUCH!

without this, i was going to finagle multiple results onto one table
dynamically, so this is basically going to save a ton of work and many
lines of code.

thank you VERY MUCH, i am greatly indebted.

On Jul 10, 4:16 pm, "Joe C." <jsk.c...@gmail .comwrote:

hello, just joined the group, i've run into a wall and am seeking some
help.

here is my query:

SELECT
DATEPART(year, TTmain.TTDate) AS 'Year',
SUM(TTmain.TTAm t) + SUM(TTmain.TTFe e) AS 'Total'
FROM
TTmain INNER JOIN TTdealers ON
TTmain.dealerID = TTdealers.deale rID
GROUP BY
DATEPART(year, TTmain.TTDate)

basically, it's summing the values of a column, then grouping by year,
results look like this:

Year | Total
----------------------------
2007 | 194309.9
2008 | 418763.24

i need to include another column, which is a summation of the "Total"
value (SUM(nsfAmt) + SUM(nsfFee) AS 'Total') but filtered based on
customer type.

so, suppose the query would look something like this (this is not
correct, it's for illustration only):

SELECT
DATEPART(year, TTmain.TTDate) AS 'Year',
SUM(TTmain.TTAm t) + SUM(TTmain.TTFe e) AS 'Total',
SUM(TTmain.TTAm t) + SUM(TTmain.TTFe e) AS 'Different Total' where
TTdealers.deale rID = 1
FROM
TTmain INNER JOIN TTdealers ON
TTmain.dealerID = TTdealers.deale rID
GROUP BY
DATEPART(year, TTmain.TTDate)

so, the "Total" result is without any conditions, and the "Different
Total" is with a condition of TTdealers.deale rID = 1. can the sum of
both values be included on the same row of the result?

i'm not sure if this possible (or if the question i'm posing even
makes sense), but thought to bounce it off the pros on this board.

any help would be greatly appreciated.