CODEWITHSUNDEEP
sqlsql-server-2008
Ben Barter
Ben Barter

Reputation: 159

UNION SQL - Adding a new column to results each time a SELECT statement is called

The following code compiles several SELECT statements.

SELECT b.User, SUM(i.ClientFee - i.CentreFee) as '1 Jan - 31 Jan'
FROM tblInvoices i
Inner Join tblBooking b
    ON i.B_ID = b.BookingID
WHERE (i.InvoiceDate BETWEEN '2012-01-01' and '2012-01-31')
GROUP BY b.User

UNION ALL

SELECT b.User, SUM(i.ClientFee - i.CentreFee) as '1 Feb - 29 Feb'
FROM tblInvoices i
Inner Join tblBooking b
    ON i.B_ID = b.BookingID
WHERE (i.InvoiceDate BETWEEN '2012-02-01' and '2012-02-29')
GROUP BY b.User

UNION ALL

SELECT b.User, SUM(i.ClientFee - i.CentreFee) as 'Total'
FROM tblInvoices i
Inner Join tblBooking b
    ON i.B_ID = b.BookingID
WHERE (i.InvoiceDate BETWEEN '2012-01-01' and '2012-02-29')
GROUP BY b.User;

Results:

User | 1 Jan - 31 Jan
----------------------
Ben  | 100.00
Tom  | 210.00
Fred | 100.00
Ben  | 250.00
Tom  | 140.00
Fred | 150.00
Ben  | 350.00
Tom  | 350.00
Fred | 250.00

I am selecting the user (b.User) and their monthly profit (i.clientfee - i.centrfee) from several tables in my company database. For each SELECT statement run I need a new column added to display this statements results. The title of these columns need to be the period start and end dates, please see table below:

User | 1 Jan - 31 Jan | 1 Feb - 29 Feb | Total
________________________________________________
Ben  |-----100.00-----|-----250.00-----| 350.00
Tom  |-----210.00-----|-----140.00-----| 350.00
Fred |-----100.00-----|-----150.00-----| 250.00

How would I achieve these results?

Any help or suggestions would be appreciated.

Upvotes: 1

Views: 1504

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269603

You don't want to use union. Use conditional aggregation:

SELECT b.User,
       SUM(case when i.InvoiceDate BETWEEN '2012-01-01' and '2012-01-31'
                then i.ClientFee - i.CentreFee
           end) as "1 Jan - 31 Jan",
       SUM(case when i.InvoiceDate BETWEEN '2012-02-01' and '2012-02-29'
                then i.ClientFee - i.CentreFee
           end) as "1 Feb - 29 Feb",
       SUM(i.ClientFee - i.CentreFee) as Total
FROM tblInvoices i
Inner Join tblBooking b
    ON i.B_ID = b.BookingID
WHERE (i.InvoiceDate BETWEEN '2012-01-01' and '2012-02-29')
GROUP BY b.User;

Also, I changed the column aliases to use double quotes instead of single quotes. Although single quotes are allowed, I much prefer limited their use to string constants. You can chose either double quotes or square braces in SQL Server, so there are better alternatives.

Upvotes: 3

Related Questions