Excel and SQL, order by help

Question

Im stuck in Excel 2007, running a query, it worked until I wanted to add a 2nd row containing "field 2".

Select "Site Updates"."Posted By", "Site Uploaded"."Site Upload Date"
From site_info.dbo."Site Updates"
Where ("Site Updates"."Posted By") AND "Site Uploaded"."Site Upload Date">={ts '2010-05-01 00:00:00'}), ("Site Location"='Chicago')
Union all
Select "Site Updates"."Posted By", "Site Uploaded"."Site Upload Date"
From site_info.dbo."Site Updates"
Where ("Site Updates"."Posted By") AND "Site Uploaded"."Site Upload Date">={ts '2010-05-01 00:00:00'}), ("Site Location"='Denver')
Order By "Site Location" ASC;

Basically I want 2 different cells for the locations, example

name - Chicago - denver
user1 - 100 - 20
user2 - 34 - 1002

Right now for some odd reason, its combining it like:

name - chicago
user1 - 120
user2 - 1036

Please note updating to 2010 beta is not a viable option for me at this point. Any and all input that will help me is greatly apprecaited. I have read over http://www.techonthenet.com/sql/order_by.php however its not gotten me very far in this question. If you have another SQL resource you recomend for people trying to get their feet wet, I'd greatly apprecaite it.

If it helps all the info is on the same table.

Answer 1

I'm not sure what you're looking for - the standard way to express such relations is top-down, not left-right, but maybe that's not the point.

Normal sql would be

SELECT col1, col2, ...
FROM table
WHERE xydate > '...'
AND ("Site Location"='Chicago'
 OR "Site Location"='Denver')
ORDER BY ...

or

AND ("Site Location" IN ('Chicago', 'Denver'))

I don't know why you get sums, and I didn't knew that you may use SQL in Excel, but I can recoomend the postgresql-guide http://developer.postgresql.org/pgdocs/postgres/index.html - but unfortunately the sql-dialetcs vary from db to db - maybe it isn't that helpful for you.

Answer 2

If what you are trying to return is the number of site updates by "Posted By" and "Site Location", then you could try something like the following.

SELECT "Posted By", "Site Location", COUNT(*) AS "TotalUpdates"
FROM "Site Updates"
WHERE "Site Updated" >= '2010-05-01 00:00:00'
GROUP BY "Posted By", "Site Location"