CiviCRM Community Forums (archive)

USE supporx4_civicrm;

-- Individual contributions
SELECT
--    CD.contribution_type_id, CD.source,
    CD.contact_id, C.display_name AS contact, C.first_name, C.last_name
    , C.is_opt_out, P.phone, C.do_not_phone
    , C.email_greeting_display, E.email, C.do_not_email
    , C.postal_greeting_display, A.street_address, A.supplemental_address_1, A.city, S.abbreviation AS state, A.postal_code AS zip_code, A.postal_code_suffix AS zip_plus_4
    , CD.receive_date, CD.total_amount
    , `COUNT(id)` AS num_contact_contributions, `SUM(total_amount)` AS sum_total_contact_contributions
FROM civicrm_contribution CD
    INNER JOIN civicrm_contact C ON CD.contact_id = C.id
    LEFT OUTER JOIN civicrm_address A ON CD.contact_id = A.contact_id AND A.is_primary = 1
    LEFT OUTER JOIN civicrm_state_province S ON A.state_province_id = S.id
    LEFT OUTER JOIN civicrm_phone P ON CD.contact_id = P.contact_id AND P.is_primary = 1
    LEFT OUTER JOIN civicrm_email E ON CD.contact_id = E.contact_id AND E.is_primary = 1
    INNER JOIN (
        SELECT
        contact_id
        , COUNT(id)
        , SUM(total_amount)
        FROM civicrm_contribution
        WHERE
            contribution_type_id IN (1, 5, 8 )
            AND receive_date BETWEEN '2011-01-01 00:00:00' AND '2011-12-31 23:59:59'         -- Calendar year 2011
        GROUP BY
            contact_id
    ) AS CDT ON CD.contact_id = CDT.contact_id
WHERE
    CD.contribution_type_id IN (1, 5, 8 )
    AND CD.receive_date BETWEEN '2011-01-01 00:00:00' AND '2011-12-31 23:59:59'     -- Calendar year 2011
ORDER BY
    CD.contact_id
    , CD.receive_date ASC
;