I have a small Video rental database and I am supposed to display in percentage the number of videos that are rented out.
So I have the formula
(TotalVideosCheckedOut / TotalVideos)*100
That was the easy part. My problem is I am not sure how I get SQL to do that. I get the totalVideos from the inventory table like
Select COUNT(VideoID) as TotalVideos FROM VideoInventory
then I was thinking maybe a subquery
(Select Count(VideoID) AS VideosCheckedOut FROM RentalHistory WHERE DateReturned is Null)
But how do I get SQL to calculate the 2 values? I am not sure where to put that. Any Ideas?
DECLARE @VideoCount Decimal
DECLARE @RentalCount Decimal
SELECT @Videocount = COUNT(VideoID) as TotalVideos FROM VideoInventory
SELECT @RentalCount = Count(VideoID) FROM RentalHistory WHERE DateReturned is Null
SELECT (@RentalCount / @VideoCount) *100 as [% Rented]
I have to turn this into a subquery:
SELECT c.CustomerID, o.OrderDate
FROM orders o RIGHT OUTER JOIN Customers c
ON o.CustomerID = c.CustomerID
WHERE o.OrderDate IS NULL;
But do not know how? I don’t understand why this is not working:
SELECT c.CustomerID, o.OrderDate FROM orders o RIGHT OUTER JOIN Customers c
ON o.CustomerID = c.CustomerID
WHERE EXISTS
( SELECT c.CustomerID, o.OrderDate FROM orders o RIGHT OUTER JOIN Customers c
ON o.CustomerID = c.CustomerID
WHERE o.OrderDate IS NULL);
Can someone help?
Thanks…
I HAVE to use the EXISTS predicate
EXISTS predicate is usually used with correlated subqueries.
Your subquery does not have reference to the main query, so you get all rows from main query.
I have a table which has video_id and cat_id, I would like to select 5 viedeos of each categories. How do I make this query work,
I got this error for my query with sub query, IN and LIMIT
"This version of MySQL doesn’t yet support ‘LIMIT & IN/ALL/ANY/SOME subquery’ " Please let me know if there is a way to write the query
table picked_videos
- video_id
- cat_id
Thaks in andvance.
this is for MySQL – sorry not mentioned earlier.
Yeah, it’s a tricky one. MySQL doesn’t directly support limiting of groups.
Try this:
SELECT * FROM picked_videos a1 WHERE 5 > (SELECT COUNT(*) FROM picked_videos a2 WHERE a2.cat_id=a1.cat_id AND a2.video_id > a1.video_id);
However, it’s much more readable if you do this in your program, i.e.
$cats = mysql_query(SELECT DISTINCT(cat_id) FROM picked_videos)
for each $c in $cats {
$vids = mysql_query(SELECT video_id FROM picked_videos WHERE cat_id=$c LIMIT 5)
}
View the page and source at:
http://nlmphotography.net/MAIN/news/news.php
[CODE]<?php
header("Content-type: text/xml");
$host = "xxxx";
$user = "xxxx";
$pass = "xxxx";
$database = "xxxx";
$linkID = mysql_connect($host, $user, $pass) or die("Could not connect to host.");
mysql_select_db($database, $linkID) or die("Could not find database.");
$query = "SELECT DISTINCT year_id, date FROM news ORDER BY year_id";
$resultID = mysql_query($query, $linkID) or die("Data not found.");
$xml_output = "<?xml version=\"1.0\" encoding=\"iso-8859-1\" standalone=\"yes\"?>\n";
$xml_output .= "<newsmodule>\n";
while ($row = mysql_fetch_array($resultID))
{
$xml_output .= "\t<year name=\"" .date(’Y', strtotime($row[date])). "\">\n";
$year_id = $row[0];
$subQuery = "select * from news where year_id =’" .$year_id. "’ order by id DESC";
$res = mysql_query($subQuery);
while ($subRow = mysql_fetch_array($res))
{
$xml_output .= "\t\t<news>\n";
$xml_output .= "\t\t\t<date>" .date(’M d’, strtotime($subRow[date])). "</date>\n";
$xml_output .= "\t\t\t<title><![CDATA[" .$subRow["title"]. "]]></title>\n";
$xml_output .= "\t\t\t<info>\n";
$xml_output .= "\t\t\t\t<![CDATA[" .$subRow["info"]. "]]>\n";
$xml_output .= "\t\t\t</info>\n";
$xml_output .= "\t\t</news>\n";
}
$xml_output .= "\t</year>\n";
}
$xml_output .= "</newsmodule>";
echo $xml_output;
?>[/CODE]
Here is an SQL dump.
[CODE]
SET NAMES latin1;
SET FOREIGN_KEY_CHECKS = 0;
CREATE TABLE `news` (
`id` int(5) NOT NULL auto_increment,
`date` date NOT NULL,
`title` varchar(80) NOT NULL,
`info` varchar(80) NOT NULL,
`year_id` year(4) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=3 DEFAULT CHARSET=utf8;
insert into `news` values(’1′,’2009-04-08′,’NLM Photography is LIVE!’,'Welcome to the new website! Have a look around as more is on the way!’,'2009′),
(’2′,’2009-04-09′,’New Links added to the links!’,'Visit our web host/designer Z Web Design!’,'2009′);
SET FOREIGN_KEY_CHECKS = 1;
[/CODE]
Thanks Chris later I will be adding functions to add records.
[RESOLVED]
Code line change:
$query = "SELECT DISTINCT year_id FROM news ORDER BY year_id";
Okay first off, in an mysql_query, you dont need the $linkID unless you are changing databases, and even then you mysql_select_db. So $resultID = mysql_query($query, $linkID) or die("Data not found.") is not wholly needed, you can remove everything passes $query and then put the parenthesis ‘$resultID = mysql_query($query);’.
And since your script looks fine to me, I am guess that in your database, your news is doubled up. Since the php only activates while the database is full.. 4 instead of your desired two, i could be wrong, should mean that you have a double-up in your news table.
**And for the person who answered above, that wouldn’t be the problem. That problem would only happen if he activated BOTH of the variables, because, as you see, each mysql query is in its own separate variable**
I need to retrieve Country name and total value of all sales that I have made to that country during 2007 where the total values of sales to that country is under $5,000. Result set should have just one row per country name.
I have a Orders table, OrderDetails (from Northwind db)
I keep getting errors from following statement (ERROR Line 1: An aggregate may not appear in the WHERE clause unless it is in a subquery contained in a HAVING clause or a select list, and the column being aggregated is an outer reference.)
SELECT ShipCountry AS ‘Country’, SUM(UnitPrice) As ‘Total Value’
FROM Orders INNER JOIN OrderDetails
ON Orders.OrderID = OrderDetails.OrderID
Where YEAR(OrderDate) = 2007
AND Sum(UnitPrice) < 5000
GROUP By ShipCountry
HELP PLEASE!
If you want to use a rolled-up value as part of row criteria, use HAVING instead of WHERE. In your example:
SELECT ShipCountry AS ‘Country’, SUM(UnitPrice) As ‘Total Value’
FROM Orders
INNER JOIN OrderDetails ON Orders.OrderID = OrderDetails.OrderID
WHERE YEAR(OrderDate) = 2007
GROUP BY ShipCountry
HAVING SUM(UnitPrice) < 5000
I know I should know this, but I’m drawing an absolute blank and don’t have time to figure it out.
A table has a name field, time field, and date field.I need to get the name associated with the max time on the max date and need to do it in one query. This can be in a subquery if needed, but I cannot use any # tables.
Example:
Name Time Date
John 0830 12/10/2008
Paul 1000 12/10/2008
George 1615 12/8/2008
Ringo 1200 12/9/2008
Since Paul had the max time on the max date, I need that record. Max time from the max date in the table.
I tried using a subquery but I’m missing something.
Any ideas?
SELECT rec_name FROM tbl_data WHERE rec_time = (SELECT MAX(rec_time) FROM tbl_data WHERE rec_date = (SELECT MAX(rec_date) FROM tbl_data));
Or
I know I should know this, but I’m drawing an absolute blank and don’t have time to figure it out.
A table has a name field, time field, and date field.I need to get the name associated with the max time on the max date and need to do it in one query. This can be in a subquery if needed, but I cannot use any # tables.
Example:
Name Time Date
John 0830 12/10/2008
Paul 1000 12/10/2008
George 1615 12/8/2008
Ringo 1200 12/9/2008
Since Paul had the max time on the max date, I need that record. Max time from the max date in the table.
I tried using a subquery but I’m missing something.
Any ideas?
SELECT rec_name FROM tbl_data WHERE rec_time = (SELECT MAX(rec_time) FROM tbl_data WHERE rec_date = (SELECT MAX(rec_date) FROM tbl_data));
Or
I’m having some trouble with an insert using a subquery. I have it half working… my problems start when I try to add information into additional fields that aren’t pulled by the subquery.
What I have right now:
INSERT INTO `phpbb_users`
(user_id, username, user_password, user_regdate, user_email, user_website, user_from, user_avatar)
(SELECT u.id, u.forum_user, u.password, UNIX_TIMESTAMP(u.date_created), u.email, p.web, p.hometown, u.avatar FROM users u JOIN profiles p ON u.id=p.user_id WHERE u.id = 4)
Now that all works just fine. But I also need to add info that isn’t already stored elsewher in the database into some additional fields in the phpbb_users table. So I came up with this:
INSERT INTO `phpbb_users`
(user_id, username, user_password, user_regdate, user_email, user_website, user_from, user_avatar, user_avatar_type, user_notify, user_notify_pm, user_popup_pm, user_timezone, user_attachsig)
((SELECT u.id, u.forum_user, u.password, UNIX_TIMESTAMP(u.date_created), u.email, p.web, p.hometown, u.avatar FROM users u JOIN profiles p ON u.id=p.user_id WHERE u.id = 4), 2, 0, 0, 1, ‘-5.00′, 1)
Unfortunately, this gives me a syntax error, and I’m not sure where my syntax is screwed up. If it helps, the error I’m getting is as follows:
#1064 – You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘(SELECT u.id, u.forum_user, u.password, UNIX_TIMESTAMP(u.date_created), u.email,’ at line 3
Yay! It works now! Thanks, truthbetold and noonehome2day.
Instead of adding the values after the select clause, you add them in the select clause, like so:
SELECT …. p.hometown, u.avatar, ‘2′, ‘0′, ‘0′, ‘1′, ‘-5.00′, ‘1′ FROM users u ….
I need to do a correlated subquery to calculate sums.
I have a table similar to this
Group | Number
A | 100
B | 23
A | 15
B | 17
B | 20
and I need to return the results like
Sum of A | Sum of B
———————————–
115 | 60
I know I need to do a subquery, however I don’t know the syntax for SQL Server 2005
is the assignment to make a subquery? Because you really don’t need to. this will work perfectly.
SELECT SUM(CASE WHEN [GROUP] = A THEN [Number] Else 0 END) As [Sum Of A],
SUM(CASE WHEN [GROUP] = B THEN [Number] Else 0 END) AS [Sum Of B]
FROM yourTableName
Okay for you SQL/Oracle lovers…I need your help in answering a few questions….I really hope you can help.
1. What is a self join? And why would you use it?
- From what I know, correct me if I am wrong…
A self-join is a query in which a table is joined (compared) to itself. Self-joins are used to compare values in a column with other values in the same column in the same table. One practical use for self-joins: obtaining running counts and running totals in an SQL query.
Can you provide me an easier example?
Lastly, why do you use it? All I know is it use when you have a large file or large amount of data…why else would you use this?
2. How do you write a PL/SQL procedure to return a value using a subquery?
How do you write that procedure returning a value without using a subquery?
3. Interpret in writing what PL/SQL black is performing? Write and example of this….
I mean PL/SQL Block…for the last question.
I don’t know the answers to all your questions since I’m not a pro. However, for #2 maybe something like this:
Subquery (see how there is a SELECT in the SELECT?)
SELECT lastname, firstname FROM employees WHERE zipcode IN (SELECT zipcode FROM employees WHERE firstname="Bob")
Without subquery (self-join):
SELECT e1.lastname, e1.firstname FROM employees e1, employees e2 WHERE e1.zipcode = e2.zipcode AND e2.firstname = "Bob"