Sum Of Sums On Two Unrelated Tables

[cybermud]

Gang:

I have two unrelated tables that contain dates and amounts. I would like to sum these amount and group by date but I am having problems doing it with a single query. I can do it using a temp table, but I would rather do it using a single query. Perhaps someone here can help.

sPayments has a date, amount, and payment number
pPayments has a date, amount, and payment number

sPayments.PaymentNumber never equals pPayments.PaymentNumber

I would like to add sPayments.amount and pPayments.amount and group them by date, so I only have two columsn returned: Date and Amount.

Any ideas?

I am currently selecting and grouping sPayments union pPayments into a temp table, then selecting that date and sum of amount, since the union query returns each date twice, with one amount for sPayments and one amount for pPayments.

Thanks,
Cybermud

 

[SQLSister]

derived tables might be what you want.

Suppose you have

select date, sum(amount from #temp

and the #temp is filled from a union query. Use a derived table instead:

Select date, sum(amount) from
(select date, amount from table1
union select date, amount from table2)a
group by date

Questions about posting. See faq183-874

 

[cybermud]

Thanks for the advice, but I tried it and I am getting an error somewhere in the code. Here is my actual query:

SELECT 	dDate, SUM(aAmount)
FROM	(
	SELECT 	strPPADate AS dDate, strPPAPrice AS aAmount
	FROM	ParkingPaymentTransactions
	WHERE	strPPAPaymentTransactionNumber IN (
		SELECT	strPPAPaymentTransactionNumber
		FROM	ParkingPaymentTransactions
		WHERE	strPPAFacilityNumber = '1700251'
		AND	strPPADate >= (CAST(DATEPART(YYYY,GETDATE()) AS VARCHAR) + '-' + CAST(DATEPART(MM,GETDATE() - 30) AS VARCHAR) + '-01')
		AND	strPPADate <  (CAST(DATEPART(YYYY,GETDATE()) AS VARCHAR) + '-' + CAST(DATEPART(MM,GETDATE()) AS VARCHAR) + '-01')
		)
	UNION
	SELECT 	strSPADate AS dDate, strSPAPrice AS aAmount
	FROM	SalesPaymentTransactions
	WHERE	strSPAPaymentTransactionNumber IN (
		SELECT	strSPAPaymentTransactionNumber
		FROM	SalesPaymentTransactions
		WHERE	strSPAFacilityNumber = '1700251'
		AND	strSPADate >= (CAST(DATEPART(YYYY,GETDATE()) AS VARCHAR) + '-' + CAST(DATEPART(MM,GETDATE() - 30) AS VARCHAR) + '-01')
		AND	strSPADate <  (CAST(DATEPART(YYYY,GETDATE()) AS VARCHAR) + '-' + CAST(DATEPART(MM,GETDATE()) AS VARCHAR) + '-01')
	)
)
GROUP BY dDate
ORDER BY dDate

Also, one thing I noticed is that if I run the two queries that make up the UNION separately, they consist of a total of 10617 records, and if I run them together with the UNION, I only get 1142 records returned. Any ideas on that?

Thanks,
Cybermud

 

[cybermud]

I meant to say, I am getting an error on the final "GROUP BY", so I removed it, and it errored on the final "ORDER BY" SO I removed that and it errored on the last ), so there is an error somewhere and I can't figure out where.

 

[swampBoogie]

SELECT     dDate, SUM(aAmount)
FROM    (
    SELECT     strPPADate AS dDate, strPPAPrice AS aAmount
    FROM    ParkingPaymentTransactions
    WHERE    strPPAPaymentTransactionNumber IN (
        SELECT    strPPAPaymentTransactionNumber
        FROM    ParkingPaymentTransactions
        WHERE    strPPAFacilityNumber = '1700251'
        AND    strPPADate >= (CAST(DATEPART(YYYY,GETDATE()) AS VARCHAR) + '-' + CAST(DATEPART(MM,GETDATE() - 30) AS VARCHAR) + '-01')
        AND    strPPADate <  (CAST(DATEPART(YYYY,GETDATE()) AS VARCHAR) + '-' + CAST(DATEPART(MM,GETDATE()) AS VARCHAR) + '-01')
        )
    UNION ALL
    SELECT     strSPADate AS dDate, strSPAPrice AS aAmount
    FROM    SalesPaymentTransactions
    WHERE    strSPAPaymentTransactionNumber IN (
        SELECT    strSPAPaymentTransactionNumber
        FROM    SalesPaymentTransactions
        WHERE    strSPAFacilityNumber = '1700251'
        AND    strSPADate >= (CAST(DATEPART(YYYY,GETDATE()) AS VARCHAR) + '-' + CAST(DATEPART(MM,GETDATE() - 30) AS VARCHAR) + '-01')
        AND    strSPADate <  (CAST(DATEPART(YYYY,GETDATE()) AS VARCHAR) + '-' + CAST(DATEPART(MM,GETDATE()) AS VARCHAR) + '-01')
    )
) dt
GROUP BY dDate
ORDER BY dDate

 

[SQLSister]

What you didn't do was alias the derived table.

Questions about posting. See faq183-874