Can you help my SQL?

September 10, 2008 5:05 AM

SQL/MS Visual Studio help please! I have two tables. I need to present Table1 data grouped by SiteID, within time FROM and TO, on the report. Then I need to make one calculation on the report using Table 1 data totalled across all sites (and within FROM and TO) plus Table 2 data totalled between times FROM and TO.

I can get the data independently:

SELECT Table1.SiteID AS SiteID, SUM(Table1.Stat1) AS Stat1, SUM(Table1.Stat2) AS Stat2, SUM(Table1.Stat3) AS Stat3
FROM Table1
WHERE (Table1.Date >= @From) AND (Table1.Date <= @To)
GROUP BY Table1.SiteID
ORDER BY Table1.SiteID;

SELECT SUM(Table2.Stat3) AS Stat3
FROM Table2
WHERE (Table2.Date >= @From) AND (Table2.Date <= @TO)

(FROM and TO are set up as Report Parameters; I can select their values when I run the report.)
But when it comes to reports, I (frankly) have no idea what I'm doing. In theory, these two SELECTs should in one with a JOIN in there, but I'm having trouble visualizing what it should be. If I relate Table1 and Table2 by the date, I end up having things double counted it looks like?

I won't be offended if you talk to me like I'm a 2 year old; I'm picking all this up as I go (if that's not already obvious). If I've left anything out that would be helpful, please say so. I'm using Visual Studio 2005. (And for now, I'm just looking for something that'll work, doesn't have to be the best solution...)
Thanks!

Table info (relevant columns):
Table 1:
SiteID, Stat1, Stat2, Date

Table 2:
UnrelatedSiteID, Stat3, Date

posted by inigo2 to Computers & Internet (10 answers total)

You should be able to pull this off with a UNION. I don't have a good schema editor on this machine so you'll have to patch up the syntax as needed:

SELECT SUM(Stat1), SUM(Stat2), SUM(Stat3) FROM

(SELECT Table1.SiteID AS SiteID, SUM(Table1.Stat1) AS Stat1, SUM(Table1.Stat2) AS Stat2, SUM(Table1.Stat3) AS Stat3
FROM Table1
WHERE (Table1.Date >= @From) AND (Table1.Date <> GROUP BY Table1.SiteID
ORDER BY Table1.SiteID) AS Table1
UNION
(SELECT SUM(Table2.Stat3) AS Stat3
FROM Table2
WHERE (Table2.Date >= @From) AND (Table2.Date <= @TO)) AS Table2
posted by 0xFCAF at 5:30 AM on September 10, 2008

Best answer: Unless I'm misunderstanding, you need to a) summarize Table1 data by SiteID, and b) total Table1 data and Table2 data. If so, I think the easiest way to accomplish this in a SQL 2005 report is:

SELECT T1.SiteID, SUM(T1.Stat1) AS Stat1, SUM(T1.Stat2) AS Stat2, T2.Stat3
FROM Table1 T1 JOIN
(SELECT SUM(Stat3) AS Stat3
FROM Table2
WHERE Date BETWEEN @From AND @To) T2
ON 1=1
WHERE T1.Date BETWEEN @From AND @To
GROUP BY T1.SiteID, T2.Stat3
ORDER BY T1.SiteID

It's an unpleasant query, but since you only need the total from Table2, you just carry it along in every row. You display the Table1 stats as you go, and then in your grand totals at the bottom, instead of displaying SUM(Stat3), you display FIRST(Stat3). It's ugly, but it ought to work.

(And I don't think you can use a UNION here since you don't want the same number of columns from both tables.)
posted by uncleozzy at 5:44 AM on September 10, 2008

Response by poster: uncleozzy, I'm trying what you put in there, but having an issue (and a question). Question -- the 2nd line there, "FROM Table1 T1 JOIN" -- should both "Table1" and "T1" be there? Am I missing something?

If I just put Table1 in there once, and try to run the query, it's telling me that Stat3 is an invalid column name. I've checked and rechecked the spelling, and it's right; any other thoughts what might be going wrong here?

Thanks to both of you!!
posted by inigo2 at 7:14 AM on September 10, 2008

Response by poster: It looks like the Stat3 inside the second SELECT statement works, but it's not liking the Table2.Stat3 in the first SELECT or in the GROUP BY.

Frustrating...
posted by inigo2 at 7:16 AM on September 10, 2008

Response by poster: Ok, I am dumb. Just realized the join is a "T1 JOIN". Trying that now.
posted by inigo2 at 7:18 AM on September 10, 2008

Yeah, I tried this before I posted it, it definitely ought to work if my assumptions about your goals are right. I just typed the "T1" alias out of force of habit; it's entirely unnecessary (so long as you don't try to call the table by that name in the query).
posted by uncleozzy at 7:28 AM on September 10, 2008

Response by poster: SUCCESS!
Thank you a ton. And now hopefully I can apply this to a few other reports I'm making...
posted by inigo2 at 7:58 AM on September 10, 2008

Response by poster: Hmm...If anyone's still poking around here, I do have another question:

Table1, SiteID, SiteName
Table2, SiteID, Stat1, Date
Table3, SiteID, Stat2, Date

The SiteIDs are the same between the three tables.
I want the resulting report to show: Site, SiteName, Sum(Stat1) for that site between @From and @To, Sum(Stat2) for that site between @From and @To.

I've got:

SELECT Table1.SiteName, Sum(Table2.Stat1), Sum(Table2.Stat2)
FROM Table1 INNER JOIN Table 2 ON Table1.SiteID = Table2.SiteID INNER JOIN Table3 ON Table1.SiteID = Table3.SiteID
WHERE (Table2.Date BETWEEN @From AND @To) AND (Table2.Date BETWEEN @From AND @To)
GROUP BY Table1.SiteName

The problem I'm having is that it works for 1 day, but again, when I try to do multiple days it's adding stuff in that it shouldn't (I think; I just know the numbers are way too high). This is different enough from the other problem that I'm stuck. Again. Anyone still out there?
posted by inigo2 at 9:45 AM on September 10, 2008

Best answer: What you're doing there is joining all the individual data; everything from Table2 with the same SiteID as something from Table3 is being joined together. You wind up with a situation like:

SiteID  Stat1  Stat2  Table2.Date Table3.Date
1       5      5      6/1/2008    6/1/2008
1       5      22     6/1/2008    6/2/2008
1       5      7      6/1/2008    6/3/2008

And so forth, which is not what you're looking for if you want to summarize the data. In the case that each table has a unique SiteID-Date combination (in which case I would argue that you might want to combine Table2 and Table3), you can:

SELECT T1.SiteName, SUM(T2.Stat1) AS Stat1, SUM(T3.Stat2) AS Stat2
FROM Table1 T1 JOIN Table2 T2
ON T1.SiteID = T2.SiteID
JOIN Table3 T3
ON T1.SiteID = T3.SiteID
AND T2.Date = T3.Date
WHERE T2.Date BETWEEN @From AND @To
GROUP BY T1.SiteName

(Although you might have to include Date in there and group on it as well; I didn't test this.) If that's not the case, then you'll have to use subqueries to summarize the data before you join it to the site list:

SELECT T1.SiteName, T2.Stat1, T3.Stat2
FROM Table1 T1 JOIN
(SELECT SiteID, SUM(Stat1) AS Stat1
FROM Table2
WHERE Date BETWEEN @From AND @To
GROUP BY SiteID) T2
ON T1.SiteID = T2.SiteID
JOIN
(SELECT SiteID, SUM(Stat2) AS Stat2
FROM Table3
WHERE Date BETWEEN @From AND @To
GROUP BY SiteID) T3
ON T1.SiteID = T3.SiteID

posted by uncleozzy at 11:25 AM on September 10, 2008

Response by poster: uncleozzy, you have saved me twice today. I am indebted.
posted by inigo2 at 1:49 PM on September 10, 2008