SQL group by query
I have a a UNION ALL on two result sets. This results in some like below.

TOTALSTABLE

    FAMILYNAME-----FIRSTNAME------NUMBER------TOTAL
    Brown          Dave           1234        500.00
    Brown          Dave           1234        300.00
    Smith          Frank          4321        123.00
    Smith          Frank          4321        456.00

I run the following query...

    SELECT TOTALSTABLE.FAMILYNAME,
      TOTALSTABLE.FIRSTNAME,
      TOTALSTABLE.NUMBER,
      SUM(TOTALSTABLE.TOTAL) COMBINEDTOTAL
    FROM TOTALSTABLE
    GROUP BY TOTALSTABLE.FAMILYNAME,
      TOTALSTABLE.FIRSTNAME,
      TOTALSTABLE.NUMBER

Which gives me something like...

    FAMILYNAME-----FIRSTNAME------NUMBER------COMBINEDTOTAL
    Brown          Dave           1234        800.00
    Smith          Frank          4321        579.00

This is what i need. However I need to add an additional column which is null in the result i am attempting to do a UNION ALL on.

Example:

T1

    FAMILYNAME-----FIRSTNAME------NUMBER------TOTAL-------DATE
    Brown          Dave           1234        500.00      01/01/2001
    Smith          Frank          4321        123.00      01/01/2001

T2

    FAMILYNAME-----FIRSTNAME------NUMBER------TOTAL-------DATE
    Brown          Dave           1234        300.00      NULL
    Smith          Frank          4321        456.00      NULL

COMBINED (UNION ALL)

    FAMILYNAME-----FIRSTNAME------NUMBER------TOTAL-------DATE
    Brown          Dave           1234        500.00      01/01/2001
    Brown          Dave           1234        300.00      NULL
    Smith          Frank          4321        123.00      01/01/2001
    Smith          Frank          4321        456.00      NULL

I need to get the combined total like in the first example, using the date that isnt null.

Example of desired results.

    FAMILYNAME-----FIRSTNAME------NUMBER------COMBINEDTOTAL----DATE
    Brown          Dave           1234        800.00           01/01/2001
    Smith          Frank          4321        579.00           01/01/2001

Can anyone tell me what I need to do here? Cheers.
SQL group by query
===

I have a a UNION ALL on two result sets. This results in some like below.

TOTALSTABLE

    FAMILYNAME-----FIRSTNAME------NUMBER------TOTAL
    Brown          Dave           1234        500.00
    Brown          Dave           1234        300.00
    Smith          Frank          4321        123.00
    Smith          Frank          4321        456.00

I run the following query...

    SELECT TOTALSTABLE.FAMILYNAME,
      TOTALSTABLE.FIRSTNAME,
      TOTALSTABLE.NUMBER,
      SUM(TOTALSTABLE.TOTAL) COMBINEDTOTAL
    FROM TOTALSTABLE
    GROUP BY TOTALSTABLE.FAMILYNAME,
      TOTALSTABLE.FIRSTNAME,
      TOTALSTABLE.NUMBER

Which gives me something like...

    FAMILYNAME-----FIRSTNAME------NUMBER------COMBINEDTOTAL
    Brown          Dave           1234        800.00
    Smith          Frank          4321        579.00

This is what i need. However I need to add an additional column which is null in the result i am attempting to do a UNION ALL on.

Example:

T1

    FAMILYNAME-----FIRSTNAME------NUMBER------TOTAL-------DATE
    Brown          Dave           1234        500.00      01/01/2001
    Smith          Frank          4321        123.00      01/01/2001

T2

    FAMILYNAME-----FIRSTNAME------NUMBER------TOTAL-------DATE
    Brown          Dave           1234        300.00      NULL
    Smith          Frank          4321        456.00      NULL

COMBINED (UNION ALL)

    FAMILYNAME-----FIRSTNAME------NUMBER------TOTAL-------DATE
    Brown          Dave           1234        500.00      01/01/2001
    Brown          Dave           1234        300.00      NULL
    Smith          Frank          4321        123.00      01/01/2001
    Smith          Frank          4321        456.00      NULL

I need to get the combined total like in the first example, using the date that isnt null.

Example of desired results.

    FAMILYNAME-----FIRSTNAME------NUMBER------COMBINEDTOTAL----DATE
    Brown          Dave           1234        800.00           01/01/2001
    Smith          Frank          4321        579.00           01/01/2001

Can anyone tell me what I need to do here? Cheers.