|
Hi,
I have a union all query in a stored procedure.
WHat I would like to do is Sum a column and return that query to the client
How would I do this?
Malcolm
Started by Malcolm on
, 3 posts
by 3 people.
Answer Snippets (Read the full thread at stackoverflow):
MyCol FROM Table2 )
SELECT othercol1, othercol2, SUM(bar) FROM ( SELECT othercol1, othercol2, bar FROM RT UNION ALL SELECT.
MyCol FROM Table1 UNION SELECT ...
SELECT SUM(MyCol) FROM ( SELECT ...
|
|
I have two tables with date fields. Each table displays the date in Short Date format (MM/DD/YYYY).
I wrote a Union Query to combine these tables, but the Union query displays the date in General Date (long) format (MM/DD/YYYY HH:MM:SS). I would be grateful...
Started by Mike on
, 4 posts
by 4 people.
Answer Snippets (Read the full thread at stackoverflow):
Do a select on the union and format the date column at the end:
select format(foo, "short date") from ( blah union blah )
There's a nice built in Jet function you can use:
Select.
A Format property.
|
|
How can one programmatically sort a union query when pulling data from two tables? For example,
SELECT table1.field1 FROM table1 ORDER BY table1.field1 UNION SELECT table2.field1 FROM table2 ORDER BY table2.field1
Throws an exception
Note: this is be ...
Started by Curtis Inderwiesche on
, 7 posts
by 7 people.
Answer Snippets (Read the full thread at stackoverflow):
The following is a UNION query that uses an ORDER BY clause:
select supplier_id, supplier FROM table1 ORDER BY table1.field1 UNION SELECT table2.field1 FROM table2 ORDER BY field1
Does not throw an exception
SELECT....
Of explaining.
|
Ask your Facebook Friends
|
In the course of a complex database structure, I need to provide the user with a means of editing data stored in a series of tables. Although all the data types are the same, they don't line up 1:1 in their names. To alleviate this, I created a query ...
Started by A. Scagnelli on
, 4 posts
by 4 people.
Answer Snippets (Read the full thread at stackoverflow):
Access cannot know which table you mean to update....
When the query is a Union query, you cannot update data in a union query, the individual rows lose their underlying table identity.
Deletes correctly across the tables.
|
|
I would like to be able to do a query of a query to UNION an unknown number of recordset. However when doing a query-of-query dots or brackets are not allowed in record set names.
For example this fails:
<cfquery name="allRecs" dbtype="query"> SELECT...
Started by Dan Roberts on
, 5 posts
by 5 people.
Answer Snippets (Read the full thread at stackoverflow):
I could = StructNew()> <cfquery name=....
Difficult task.
Their scope in query
Create a function that accepts 2 recordsets as arguments and returns one this is very inefficient compared to doing all UNIONs in one query though.
|
|
We have a Union Query. Here's a basic (similar) example:
SELECT a.Name, b.Info FROM a LEFT JOIN b ON (a.ID = b.ID) WHERE a.Name LIKE "a%" UNION SELECT a.Name, b.Info FROM a LEFT JOIN b ON (a.ID = b.ID) WHERE a.Name LIKE "b%" ORDER BY a.Name, b.Info;
I...
Started by Christopher on
, 4 posts
by 4 people.
Answer Snippets (Read the full thread at stackoverflow):
At this point, there is only one table so to speak, so the reference to b.Info) WHERE a.Name LIKE "a%" UNION....
I think when you're doing UNION query you should specify column position, not name in the ORDER the Union has taken place.
|
|
Let's say we have a query that is essentially using a union to combine 2 recordsets into 1. Now, I need to duplicate the records by way of typically using a join. I feel option 1 is in my opinion the best bet for performance reasons but was wondering ...
Started by Dan on
, 4 posts
by 4 people.
Answer Snippets (Read the full thread at stackoverflow):
SELECT Name, Category FROM t1 JOIN t_right ON right_category = category UNION SELECT Name, Category UNION SELECT Name, Category FROM t2 ) t JOIN t_right ON right_category = category
These queries 1 t_right Category --- 1 1
The first....
|
|
Hi guys, I'm running a sql query to get basic details from a number of tables. Sorted by the last update date field. Its terribly tricky and I'm thinking if there is an alternate to using the UNION clause instead...I'm working in PHP MYSQL.
Actually I...
Started by Ali on
, 5 posts
by 5 people.
Answer Snippets (Read the full thread at stackoverflow):
It would definitely be wise to profile....
I'm not aware of an alternative to UNION that does what you want, and hopefully those fetches won't be too expensive.
With UNION given they're likely to have lots of data items which are different.
|
|
I've got this union query:
(SELECT INSTALLER, INSTALLTIME, RESULT, JOBNUMBER, HONAME, ADDRESS, CITY, STATE, ZIP, NOTES, SMNOTES, '' as priority, PAFS, upsell, TERM, MMRUPGRADE, WARRANTY, EFT FROM ACCOUNTS WHERE INSTALLDATE = '$date' && FUNDINGSTATUS !...
Started by mlebrun15 on
, 4 posts
by 4 people.
Answer Snippets (Read the full thread at stackoverflow):
To lookup the query_id
show profiles;
And to see the execution plan:
show profile for query x FUNDINGSTATUS in the first where clause will make that query not utilize the index?
Yes, the index will still for fundingstatus , it may....
|
|
The query:
SELECT ct_cid as level1 FROM cat_tree WHERE ct_sid=$sid_int AND ct_parent =$cid_int) UNION (SELECT ct_cid as level2 FROM cat_tree WHERE ct_sid=$sid_int AND ct_parent IN level1)
The query is supposed to find the child nodes up to 2 levels of...
Started by Nir on
, 4 posts
by 4 people.
Answer Snippets (Read the full thread at stackoverflow):
Select 1 as LevelID, CT_CID from cat_tree where ct_sid = $sid_... .
Redo the query as a sub-select in the SECOND query...
UNION select 2 of the first...
You may want is something like
select 1 as LevelID, CT_CID from cat_tree where.. .
|
