CODEWITHSUNDEEP
t-sqlgroup-bymultiple-columns
George Eivaz
George Eivaz

Reputation: 57

TSQL Group based on 2 conditions in the same Recordset

I have a question which has been confusing me to 2 days. Here is the problem. I have a table which looks something like this:

enter image description here

What I would like to do is to have to groupings that end up in the same table. For the example above, I would end up with the following:

enter image description here

Can something like this be accomplished? As you can notice, we are grouping by different columns. This does not have to be done with one query, however, it does have to end up in one table/dataset.

Upvotes: 1

Views: 163

Answers (3)

xQbert
xQbert

Reputation: 35323

SELECT SaleYear year, 
  0 earned, 
  sum(Sale) sale
FROM yourtable
group by saleyear
UNION
Select PeriodEarnedYear,
    sum(PeriodEarned) Earned,
    Sale
from yourtable
group by PeriodEarnedYear

But this makes a few assumptions.

Upvotes: 0

Michael Fredrickson
Michael Fredrickson

Reputation: 37388

I would sum up each set independently, and then join together using a full outer join, since it looks like you may have years with sales, but not years with earnings, or vice versa...

SELECT
    ISNULL(PeriodEarnedYear, [Sale Year]) AS [Year],
    Earned,
    Sale
FROM
(
    SELECT
        PeriodEarnedYear,
        SUM(PeriodEarned) AS Earned
    FROM YourTable
    GROUP BY PeriodEarnedYear
) p FULL OUTER JOIN
(
    SELECT
        [Sale Year],
        SUM(Sale) AS Sale
    FROM YourTable
    GROUP BY [Sale Year]
) s ON p.PeriodEarnedYear = s.[Sale Year]

Upvotes: 2

Taryn
Taryn

Reputation: 247680

Seems like you need this:

select PeriodEarnedYear,
    sum(PeriodEarned) Earned,
    Sale
from yourtable
group by PeriodEarnedYear, Sale

Upvotes: 0

Related Questions