homepage Welcome to WebmasterWorld Guest from 54.226.93.128
register, free tools, login, search, subscribe, help, library, announcements, recent posts, open posts,
Pubcon Platinum Sponsor 2014
Home / Forums Index / Code, Content, and Presentation / Databases
Forum Library, Charter, Moderators: physics

Databases Forum

    
MYSQL: How to COUNT Union of 2 SELECT stmnts that have diff. columns
NeedExpertHelp




msg:4052792
 3:22 am on Jan 2, 2010 (gmt 0)

Hello,

I've been trying for hours to do a COUNT of a UNION of two SELECT statements that have different columns (the 2nd SELECT statement being united has 1 extra column criteria that the first one does not), but I'm getting the following error:

"#1222 - The used SELECT statements have a different number of columns"

How do I go about counting a union of select statements with different columns?

More specifically, how would I go about doing a COUNT for the QUERY below such that I get the number of rows returned by that query and nothing else?

Thanks a lot!

-----------------

HERE IS MY ORIGINAL QUERY:

SELECT name1 . * , name2 . *
FROM table1 AS name1
LEFT JOIN table2 AS name2
USING (
col1
)
LEFT JOIN table3 AS name3
USING (
col1
)
WHERE name1.col1 != 'A'
AND name3.col2 = 'B'
AND name3.col3 > ( UNIX_TIMESTAMP( ) -7200 )
AND name1.col4 LIKE 'C'
AND name1.col5 LIKE 'D'";
AND name1.col6 = 'E'
GROUP BY name1.col1
UNION (
SELECT name1 . * , name2 . *
FROM table1 AS name1
LEFT JOIN table2 AS name2
USING (
col1
)
LEFT JOIN table3 AS name3
USING (
col1
)
WHERE name1.col1 != 'A'
AND name3.col2 = 'B'
AND name3.col3 > ( UNIX_TIMESTAMP( ) -7200 )
AND name1.col4 LIKE 'C'
AND name1.col5 LIKE 'D'
GROUP BY name1.col1
)

 

NeedExpertHelp




msg:4052916
 3:11 pm on Jan 2, 2010 (gmt 0)

Any ideas?

syber




msg:4053061
 11:34 pm on Jan 2, 2010 (gmt 0)

By design, all SELECTS in a UNION must have the same number of columns. To overcome this - simply add a dummy column to the first SELECT. This dummy column must be of similar datatype to the extra data column in the second SELECT.

SELECT name1 . * , name2 . * , ' ' as dummy
......
......
UNION
SELECT name1 . * , name2 . *
.....
.......

NeedExpertHelp




msg:4053074
 1:02 am on Jan 3, 2010 (gmt 0)

Hi syber,

Thanks for the reply.

How would I got about implementing that in my original query above considering that the extra column is being referenced as a criteria (AND name1.col6 = 'E' ) ?

syber




msg:4053101
 3:14 am on Jan 3, 2010 (gmt 0)

It doesn't matter how the columns are being referenced, when you say name1.* and name2.* - you are bringing in ALL the columns from name1 and name2.

If you are only interested in a COUNT, you could make it easier on yourself by changing the query to say:

SELECT COUNT(*)
FROM
(
SELECT name1.col1
FROM table1 AS name1
LEFT JOIN table2 AS name2
......
......
UNION
SELECT name1.col1
FROM table1 AS name1
LEFT JOIN table2 AS name2
.....
.......

) AS t1

NeedExpertHelp




msg:4054897
 3:10 am on Jan 6, 2010 (gmt 0)

syber, you're a genius! That worked! Thank you so much. Sorry about the delay as I was on holidays.

I had one last question if you could be so kind.

In my original query, how can I "ORDER BY name1.col7 DESC" ?

When I try to add that at the very end of the original query, I get the following error message:

"Table 'table1' from one of the SELECTs cannot be used in global ORDER clause"

How can I order the entire united results by name1.col7?

Thanks again!

NeedExpertHelp




msg:4054969
 7:25 am on Jan 6, 2010 (gmt 0)

Actually, let me be clearer.

I want << each >> SELECT to be ORDER BY name1.col7 DESC, not the united results.

Basically, as you can probably tell, I'm trying to unite two SELECTS, one with a stricter (e.g. additional) criteria and one without so that I have more results, displaying the ones that fit the stricter criteria first, and then the rest, but I want each group to be sorted by name1.co7, does that make sense?

That additional criteria in the first SELECT (AND name1.col6 = 'E') is to match users from their country, followed by ueers from any country, so I want to prioritize users from the same country, while still showing the full results for users from all countries, but only after I've displayed the users from the same country first. THEN, I want to sort each group separately by a criteria nama1.col7.

I hope that makes sense. Perhaps there's an easier way to accomplish this and I'm over-complicating it.

syber




msg:4054986
 8:42 am on Jan 6, 2010 (gmt 0)

If I understand you correctly, it would look like this:

SELECT name1.col7 AS country, 1 AS sort
FROM table1 AS name1
LEFT JOIN table2 AS name2
......
......
UNION
SELECT name1.col7, 2 AS sort
FROM table1 AS name1
LEFT JOIN table2 AS name2
.....
.......
ORDER BY country DESC, sort

Since you are doing a UNION, the ORDER BY clause only makes sense for the combined results. I assume you are not looking for a COUNT in this instance, so you would need to add whatever additional columns you would like to display to each SELECT statement

NeedExpertHelp




msg:4055284
 4:47 pm on Jan 6, 2010 (gmt 0)

Thanks for your reply, syber.

I'm confused a bit about how to implement that in my original query and how exactly it would work.

Also, name1.col7 is not the country, it's the user's last login. The Country is the "AND name1.col6 = 'E'" criteria which only appears in the top SELECT (before the UNION).

My original query is:


1: SELECT name1 . * , name2 . *
2: FROM table1 AS name1
3: LEFT JOIN table2 AS name2
4: USING (
5: col1
6: )
7: LEFT JOIN table3 AS name3
8: USING (
9: col1
10: )
11: WHERE name1.col1 != 'A'
12: AND name3.col2 = 'B'
13: AND name3.col3 > ( UNIX_TIMESTAMP( ) -7200 )
14: AND name1.col4 LIKE 'C'
15: AND name1.col5 LIKE 'D'";
16: AND name1.col6 = 'E'
17: GROUP BY name1.col1
18: UNION (
19: SELECT name1 . * , name2 . *
20: FROM table1 AS name1
21: LEFT JOIN table2 AS name2
22: USING (
23: col1
24: )
25: LEFT JOIN table3 AS name3
26: USING (
27: col1
28: )
29: WHERE name1.col1 != 'A'
30: AND name3.col2 = 'B'
31: AND name3.col3 > ( UNIX_TIMESTAMP( ) -7200 )
32: AND name1.col4 LIKE 'C'
33: AND name1.col5 LIKE 'D'
34: GROUP BY name1.col1
35: )

Which lines would I modify to sort each SELECT by name1.col7 (which is the last login)?

For an example of this, let's say the user is from the US and their results without sorting produced:

RESULT A
Username - Country - Last Login
USER1 US 3 min ago
USER7 US 1 min ago
USER4 US 7 min ago
USER8 CA 8 min ago
USER9 ZA 5 min ago
USER2 RU 1 min ago

That's what I get now which works fine and you helped me do the COUNT for this which was important as I needed to know how many results it produced.

But now I would like that Result A - after sorting by name1.col7 (last login) to be:

RESULT B (sorted)
Username - Country - Last Login
USER7 US 1 min ago
USER1 US 3 min ago
USER4 US 7 min ago
USER2 RU 1 min ago
USER9 ZA 5 min ago
USER8 CA 8 min ago

Notice how each SELECT is now ordered by name1.col7 (last login) while still maintaining each SELECT separate (the "US" are together, then the rest).

Right now my Original SELECT works to do RESULT A perfectly, and now I just want to do the ORDER BY to get RESULT B (sorted, each group separately).

So which lines would I modify and how to achieve RESULT B considering that right now my query gives me RESULT A?

I hope that's clearer and thanks again!

syber




msg:4055545
 10:49 pm on Jan 6, 2010 (gmt 0)

You would need to add a column to each SELECT statement in the UNION so that you can have a secondary sort column. For your example it should look something like this:

SELECT username, country, 1 AS sortcolumn
FROM .. ..
.....
UNION
SELECT username, country, 2
FROM .....
.....
ORDER BY country DESC, sortcolumn

This way, the results will be in country order and then is sortcolumn order (either a 1 or a 2). The ORDER BY clause for a UNION has to work on all the rows returned.

NeedExpertHelp




msg:4056454
 3:19 am on Jan 8, 2010 (gmt 0)

Hi Syber,

Thanks for your reply.

I think I may have found an even easier solution. Apparently, if you wrap the individual SELECT statements in parenthesis, you can do an ORDER BY for each one, as per this: [p2p.wrox.com...]

Is that correct? And if so, are there any drawbacks to doing it this way vs. the way you suggested?

Thanks again.

syber




msg:4056890
 7:30 pm on Jan 8, 2010 (gmt 0)

Technically that will execute, but I don't think it will give you the results in the order you want. Try it and see.

NeedExpertHelp




msg:4056948
 8:52 pm on Jan 8, 2010 (gmt 0)

You're right syber, it doesn't work.

I tried your solution, but the problem I'm encountering is that duplicates are appearing.

In other words, usernames that match the county and appear in the first SELECT are also appearing in the second SELECT which encompasses all countries.

I suspect that the UNION isn't considering the usernames the same because of the different sortcolumn value.

How can I get around this problem so that usernames don't repeat?

Thanks!

Demaestro




msg:4056959
 9:12 pm on Jan 8, 2010 (gmt 0)

It's ugly, slow and annoying to maintain but this will work... basically you put the entire orig query as a sub-select and it will return the count on every row in your query. Performance will be an issue.

SELECT
COUNT(SELECT name1 . * , name2 . *
FROM table1 AS name1
LEFT JOIN table2 AS name2
USING (
col1
)
LEFT JOIN table3 AS name3
USING (
col1
)
WHERE name1.col1 != 'A'
AND name3.col2 = 'B'
AND name3.col3 > ( UNIX_TIMESTAMP( ) -7200 )
AND name1.col4 LIKE 'C'
AND name1.col5 LIKE 'D'";
AND name1.col6 = 'E'
GROUP BY name1.col1
UNION (
SELECT name1 . * , name2 . *
FROM table1 AS name1
LEFT JOIN table2 AS name2
USING (
col1
)
LEFT JOIN table3 AS name3
USING (
col1
)
WHERE name1.col1 != 'A'
AND name3.col2 = 'B'
AND name3.col3 > ( UNIX_TIMESTAMP( ) -7200 )
AND name1.col4 LIKE 'C'
AND name1.col5 LIKE 'D'
GROUP BY name1.col1
) ) as the_count,

name1 . * , name2 . *
FROM table1 AS name1
LEFT JOIN table2 AS name2
USING (
col1
)
LEFT JOIN table3 AS name3
USING (
col1
)
WHERE name1.col1 != 'A'
AND name3.col2 = 'B'
AND name3.col3 > ( UNIX_TIMESTAMP( ) -7200 )
AND name1.col4 LIKE 'C'
AND name1.col5 LIKE 'D'";
AND name1.col6 = 'E'
GROUP BY name1.col1
UNION (
SELECT name1 . * , name2 . *
FROM table1 AS name1
LEFT JOIN table2 AS name2
USING (
col1
)
LEFT JOIN table3 AS name3
USING (
col1
)
WHERE name1.col1 != 'A'
AND name3.col2 = 'B'
AND name3.col3 > ( UNIX_TIMESTAMP( ) -7200 )
AND name1.col4 LIKE 'C'
AND name1.col5 LIKE 'D'
GROUP BY name1.col1
)

There are other solutions if you pass the dataset to some server side code..... for example if the dataset gets passed into an array or dictionary you can check the length of the object and use that for your count.

Something like this:
the_sql = "select * from table"
results = sql_execute(the_sql)
results_count = len(results)

OR you can call 2 queries, one does the query, one does the count of query so you have 2 results to look at.

Something like this:
the_sql = "select * from table"
the_sql_count = "select count(select * from table)"

results = sql_execute(the_sql)
results_count = sql_execute(the_sql_count)

Sometimes it is easier to get the count seperate from your original query.

NeedExpertHelp




msg:4056980
 9:44 pm on Jan 8, 2010 (gmt 0)

Demaestro, syber already solved that COUNT question in Post #5. He did a really good and straight-forward job too.

Now we're tackling a different issue, that of individually sorting united SELECT statements. Please read the posts previous to yours to getter a better idea.

Thanks.

Demaestro




msg:4056990
 9:50 pm on Jan 8, 2010 (gmt 0)

Expert.... I did read them and saw that you aren't sorting the way you wanted and you are getting duplicates with that specific solution.

The 3 solutions I presented give you a new way of counting your results and will allow you to sort as you had originally, which is why I presented them, and will not give you duplicates.

I have found that when you are trying to do something, like adding a function into SQL, if your new fix breaks something else then it is not a fix at all.

If you go down this road of hacking together logic so that you can keep this all in 1 SQL statement you are going to end up with spaghetti code. (IE what happens if your sort solution breaks your count or something else? Are you going to leave it and fix the new issue?)

My first solution is a hack IMO but it will let you do everything you want... sort and count in 1 sql statement.

My last 2 suggestions are elegant as far as code goes and will get you moving forward, you will get a count and you can sort but it won't all happen in 1 sql statement. I am not sure if 1 sql statement is a requirement for you or not

**************
the_sql = "select * from table"
results = sql_execute(the_sql)
results_count = len(results)
**************

**************
the_sql = "select * from table"
the_sql_count = "select count(select * from table)"

results = sql_execute(the_sql)
results_count = sql_execute(the_sql_count)
**************

Good Luck to you.

NeedExpertHelp




msg:4057021
 10:56 pm on Jan 8, 2010 (gmt 0)

Demaestro, thanks for the explanation but I think your solution is more complicated than what I was looking for.

I just found this on the MYSQL site which is the solution I'm looking for:

------------------------------------------
Posted by Phil McCarley on February 28 2006 6:37am

In addition to the above comment regarding the ORDERing of individual SELECTS, I was after a way to do exactly what is says wouldn't work. I have two playlists, and to get the correct order I need to use two different ORDER clauses, also I wanted to use the DISTINCT functionality of the UNION syntax.

What I needed was the contents of each playlist to be ordered in there specific way, while the first appeared wholly before the second. Also, I couldn't use the various tricks of adding extra colums to sort on because that left me with non-unique rows and therefore if the same entry was in both lists, the duplicate didn't get removed.

How I overcame this was to use subqueries, as follows:

SELECT song_id FROM
(SELECT song_id FROM play_immediate
ORDER BY play_id DESC) AS t1

UNION

SELECT song_id FROM
(SELECT song_id FROM play_later
ORDER BY play_id) AS t2

And using this I am able to sort each list differently, one ascending and one descending, keep the 'immediate' list before the 'later' list but still remove all duplicates.

Hope this helps others.
------------------------------------------

How can I implement the above into my query to get it to work?

Thanks again!

syber




msg:4057030
 11:02 pm on Jan 8, 2010 (gmt 0)


I suspect that the UNION isn't considering the usernames the same because of the different sortcolumn value.

How can I get around this problem so that usernames don't repeat?

You are correct, the UNION operation has an implied DISTINCT. Is there an east way that you can eliminate those rows in the second query?

If not, you could always do this:

SELECT DISTINCT username, country
FROM
(SELECT username, country, 1 AS sortcolumn
FROM .. ..
.....
UNION
SELECT username, country, 2
FROM .....
.....
ORDER BY country DESC, sortcolumn ) as t

Global Options:
 top home search open messages active posts  
 

Home / Forums Index / Code, Content, and Presentation / Databases
rss feed

All trademarks and copyrights held by respective owners. Member comments are owned by the poster.
Home ¦ Free Tools ¦ Terms of Service ¦ Privacy Policy ¦ Report Problem ¦ About ¦ Library ¦ Newsletter
WebmasterWorld is a Developer Shed Community owned by Jim Boykin.
© Webmaster World 1996-2014 all rights reserved