March 8, 2013 at 11:27 am
Hey,
Yeah the Units count blew up. Initialvisit was correct though ha.
This is the reason I had different datasets, because it is too complex of information spanning many different areas, to have only 1 select statement.
March 8, 2013 at 11:43 am
In which case you will have to produce several sub queries to produce totals as sugggested by pete.
Simplistically put
SELECT t.ID,u.Units,v.Initialvisit
FROM
t
LEFT JOIN (SELECT COUNT(*) AS [Units] FROM [unittable] GROUP BY ID) u ON u.ID = t.ID
LEFT JOIN (SELECT SUM(X) AS [Initialvisit] FROM [visittable] GROUP BY ID) v ON v.ID = t.ID
and repeat the joins for each subset of count/sums
Another way would be to create a temp table containing all possible output columns and do inserts into this table for each subset of data making columns not in each subset zero. Then sum the data accordingly.
Far away is close at hand in the images of elsewhere.
Anon.
March 8, 2013 at 11:55 am
David Burrows, you are a life saver.
I'm doing the joining to the dataset, acting as a table.
I'm not a beginner to SQL, but this trick was definitely not in my toolbox.
Also, thanks Pete, both of you have helped me out tremendously getting over this hurdle, it's been one of those things where I've stared at it for so long I wasn't seeing all the options out there.
I'll post again if I run into anymore issues, thanks guys.
Viewing 3 posts - 16 through 17 (of 17 total)
You must be logged in to reply to this topic. Login to reply