Inserting result of multiple query into a row in a table

Question

Post reply

Inserting result of multiple query into a row in a table

Zaza

SSC-Addicted

Points: 493
More actions
June 13, 2013 at 9:46 am

#99847

Hi,
I have 2 queries that I need the result to appear in one table (one row) and I'm struggling on how to do this
example of what I'm trying to achieve
the following query will give me the number of sales for the day (web sales), lets say the result is 4000
select transactiondate, count(distinct transactionid) as TotalwebSales
from saletransactions
where transactionstationnumber = web and transactiondate = <today>
group by transactiondate
and this other query will give me the number of sales made inhouse for the day, lets say the result is 2000
select transactiondate, count(distinct transactionid) as TotalInHouseSales
from saletransactions
where transactionstationnumber = local and transactiondate = <today>
group by transactiondate
How do I combine the 2 and insert the results in one row in one table, such as
2013/05/11 4000 2000
Eventually, I would like to have a row for each day or day range (as I will provide date range instead of just a date)
Many Thanks

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 1

SELECT

transactiondate = ISNULL(a.transactiondate,b.transactiondate),

a.TotalwebSales,

b.TotalInHouseSales

FROM (

select transactiondate, count(distinct transactionid) as TotalwebSales

from saletransactions

where transactionstationnumber = web -- ?

and transactiondate = <today> -- ?

group by transactiondate

) a

FULL OUTER JOIN (

select transactiondate, count(distinct transactionid) as TotalInHouseSales

from saletransactions

where transactionstationnumber = local -- ?

and transactiondate = <today> -- ?

group by transactiondate

) b

ON b.transactiondate = a.transactiondate

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Zaza SSC-Addicted Points: 493 More actions · Answer 2

That is great.. Many thanks...

What would be the syntax if I have 3 or more selects to join ?

Zaza SSC-Addicted Points: 493 More actions · Answer 3

Zaza

SSC-Addicted

Points: 493

June 13, 2013 at 2:04 pm

#1623889

I have found my answer.. Many thanks

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 4

You're welcome, and thanks for the feedback. Sorry I didn't get back to you on your second question, I'm on UK time.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden