Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Inserting result of multiple query into a row in a table Expand / Collapse
Author
Message
Posted Thursday, June 13, 2013 9:46 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, July 23, 2013 8:34 AM
Points: 27, Visits: 39
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



Post #1463170
Posted Thursday, June 13, 2013 9:54 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 1:36 AM
Points: 6,781, Visits: 13,981
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
Exploring Recursive CTEs by Example Dwain Camps
Post #1463176
Posted Thursday, June 13, 2013 11:32 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, July 23, 2013 8:34 AM
Points: 27, Visits: 39
That is great.. Many thanks...

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



Post #1463221
Posted Thursday, June 13, 2013 2:04 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, July 23, 2013 8:34 AM
Points: 27, Visits: 39
I have found my answer.. Many thanks


Post #1463297
Posted Friday, June 14, 2013 1:02 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 1:36 AM
Points: 6,781, Visits: 13,981
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
Exploring Recursive CTEs by Example Dwain Camps
Post #1463405
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse