db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Knut Anders Hatlen <Knut.Hat...@Sun.COM>
Subject Re: A SQL question , how to show calculated fields in Rows not in columns
Date Wed, 08 Feb 2006 13:31:38 GMT
Knut Anders Hatlen <Knut.Hatlen@Sun.COM> writes:

> Legolas Woodland <legolas.w@gmail.com> writes:
>
>> Hi
>> Thank you for reading my post.
>> imagine that i have :
>> select count(userID) ,count (siteID) from users,websites
>>
>> it will show one record that contain two columns , each column
>> represent one of the count value
>> now i want to show two record with one column , indeed i need this to
>> pass the query to a charting system . the chart will just use first
>> column of each rows.
>> so i need to have two rows with one column to use that charting system.
>
> You could try this SQL statement:
>
> SELECT COUNT(userID) FROM users UNION SELECT COUNT(siteID) FROM websites

Sorry, this is plain wrong! Thank you Bernt for pointing it out. If
the count is the same for the two tables, the union will give one row
only. Additionally, you don't know which row will come first when you
get more than one. The correct query is:

  SELECT count FROM
    (SELECT COUNT(userID), 1 FROM users
       UNION
     SELECT COUNT(siteID), 2 FROM websites) expr(count, id)
  ORDER BY id

Now you get two rows with one column. The first row is the user count
and the second one is the site count.

-- 
Knut Anders


Mime
View raw message