I have this query
SELECT 'bracket' = CASE
WHEN income BETWEEN 0 AND 49 THEN '0-49'
WHEN income BETWEEN 50 AND 99 THEN '50-99'
WHEN income BETWEEN 100 AND 499 THEN '100-499'
WHEN income BETWEEN 500 AND 1000 THEN '500-1000'
ELSE 'Other' END, count(income) AS number
FROM #persons
GROUP BY CASE
WHEN income BETWEEN 0 AND 49 THEN '0-49'
WHEN income BETWEEN 50 AND 99 THEN '50-99'
WHEN income BETWEEN 100 AND 499 THEN '100-499'
WHEN income BETWEEN 500 AND 1000 THEN '500-1000'
ELSE 'Other' END
ORDER BY min(income) ASC
which returns
bracket number
-------- -----------
50-99 4
100-499 4
500-1000 2
I want it to return this
bracket number
-------- -----------
0-49 0
50-99 4
100-499 4
500-1000 2
Other 0
Showing that there are no incomes within the 0-49 category and 0
incomes in the other category. Halp?
AND
I need to get the numbers 1-1000 into a table called #thousand using
the identity function. Help? 6 1535
Consider creating a table Brackets, which would allow:
SELECT B.bracket, count(income) AS number
FROM Brackets as B
LEFT OUTER JOIN #persons as P
ON O.income BETWEEN B.FromIncome AND B.ToIncome
GROUP BY B.bracket
ORDER BY min(income) ASC
You will have to add one for negative numbes, and another for positive
numbers 1000, both assigned 'Other' for the bracket column.
Roy Harvey
Beacon Falls, CT
On 23 Aug 2006 12:14:02 -0700, mu******@gmail. com wrote:
>I have this query
SELECT 'bracket' = CASE WHEN income BETWEEN 0 AND 49 THEN '0-49' WHEN income BETWEEN 50 AND 99 THEN '50-99' WHEN income BETWEEN 100 AND 499 THEN '100-499' WHEN income BETWEEN 500 AND 1000 THEN '500-1000' ELSE 'Other' END, count(income) AS number FROM #persons GROUP BY CASE WHEN income BETWEEN 0 AND 49 THEN '0-49' WHEN income BETWEEN 50 AND 99 THEN '50-99' WHEN income BETWEEN 100 AND 499 THEN '100-499' WHEN income BETWEEN 500 AND 1000 THEN '500-1000' ELSE 'Other' END ORDER BY min(income) ASC
which returns
bracket number -------- ----------- 50-99 4 100-499 4 500-1000 2
I want it to return this
bracket number -------- ----------- 0-49 0 50-99 4 100-499 4 500-1000 2 Other 0
Showing that there are no incomes within the 0-49 category and 0 incomes in the other category. Halp?
AND
I need to get the numbers 1-1000 into a table called #thousand using the identity function. Help?
On 23 Aug 2006 12:14:02 -0700, mu******@gmail. com wrote:
(snip)
>AND
I need to get the numbers 1-1000 into a table called #thousand using the identity function. Help?
Hi mutemode,
SELECT TOP 1000 IDENTITY(int, 1,1) AS id
INTO #ten
FROM sysobjects AS a, sysobjects AS b
--
Hugo Kornelis, SQL Server MVP
(mu******@gmail .com) writes:
I need to get the numbers 1-1000 into a table called #thousand using
the identity function. Help?
Why IDENTITY?
Here is a script for a million numbers. It's a tad slow for a temp
table, but why temp table? A table of numbers comes in handy in
several places.
CREATE TABLE Numbers (Number int NOT NULL PRIMARY KEY);
WITH digits (d) AS (
SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION
SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION
SELECT 7 UNION SELECT 8 UNION SELECT 9 UNION
SELECT 0)
INSERT Numbers (Number)
SELECT Number
FROM (SELECT i.d + ii.d * 10 + iii.d * 100 + iv.d * 1000 +
v.d * 10000 + vi.d * 100000 AS Number
FROM digits i
CROSS JOIN digits ii
CROSS JOIN digits iii
CROSS JOIN digits iv
CROSS JOIN digits v
CROSS JOIN digits vi) AS Numbers
WHERE Number 0
If you insist on exactly 1000 numbers, you can easily cut it down. It
will probably run a lot faster than when inserting a million numbers.
--
Erland Sommarskog, SQL Server MVP, es****@sommarsk og.se
Books Online for SQL Server 2005 at http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at http://www.microsoft.com/sql/prodinf...ons/books.mspx
Hugo Kornelis (hu**@perFact.R EMOVETHIS.info. INVALID) writes:
On 23 Aug 2006 12:14:02 -0700, mu******@gmail. com wrote:
(snip)
>>AND
I need to get the numbers 1-1000 into a table called #thousand using the identity function. Help?
Hi mutemode,
SELECT TOP 1000 IDENTITY(int, 1,1) AS id
INTO #ten
FROM sysobjects AS a, sysobjects AS b
IF (SELECT COUNT(*) FROM #ten) < 1000 OR
(SELECT MIN(id) FROM #ten) <1 OR
(SELECT MAX(id) FROM #ten) <1000
BEGIN
RAISERROR ('Fill of #ten failed!', 16, 1)
RETURN 1
END
That is, I don't think one should trust the code above to always return
what you looking for. Adding some paranoia can avoid incorrect results.
--
Erland Sommarskog, SQL Server MVP, es****@sommarsk og.se
Books Online for SQL Server 2005 at http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at http://www.microsoft.com/sql/prodinf...ons/books.mspx
On Wed, 23 Aug 2006 21:51:18 +0000 (UTC), Erland Sommarskog wrote:
>Hugo Kornelis (hu**@perFact.R EMOVETHIS.info. INVALID) writes:
>On 23 Aug 2006 12:14:02 -0700, mu******@gmail. com wrote:
(snip)
>>>AND
I need to get the numbers 1-1000 into a table called #thousand using the identity function. Help?
Hi mutemode,
SELECT TOP 1000 IDENTITY(int, 1,1) AS id INTO #ten FROM sysobjects AS a, sysobjects AS b
IF (SELECT COUNT(*) FROM #ten) < 1000 OR
(SELECT MIN(id) FROM #ten) <1 OR
(SELECT MAX(id) FROM #ten) <1000 BEGIN
RAISERROR ('Fill of #ten failed!', 16, 1)
RETURN 1 END
That is, I don't think one should trust the code above to always return what you looking for. Adding some paranoia can avoid incorrect results.
Hi Erland,
Some paranoia is good, but too much is, well, too much <g>
I agree with the test for a COUNT(*) of less than 1000 (though even in
an empty database, sysobjects has 47 rows so the cross join should be
good 2209 rows).
The tests for MIN and MAX remind me of the examples of "defensive
programming" I have seen when I still programmed PL/I on a mainframe. In
T-SQL equivalent, the code read something like this:
SET @SomeVariable = 15;
IF @SomeVariable <15
BEGIN;
RAISERROR ('The DBMS has a bug!', 16, 1);
END;
Assuming that the IDENTITY function works as advertised, you'll never be
able to get a situation with MIN(id) other than 1 and MAX(id) other than
1000 (assuming the COUNT(*) check is passed).
Finally, the COUNT(*) check can be replaced by a much more efficient
check for @@ROWCOUNT. The end result would be (correcting my error in
the requested table name while I'm at it:
SELECT TOP 1000 IDENTITY(int, 1,1) AS id
INTO #thousand
FROM sysobjects AS a, sysobjects AS b;
IF @@ROWCOUNT < 1000
BEGIN;
RAISERROR ('Fill of #thousand failed - not enough rows in sysobjects!
Please add an extra occurence of sysobejcts to the FROM clause.', 16,
1);
ROLLBACK TRANSACTION;
END;
--
Hugo Kornelis, SQL Server MVP
Hugo Kornelis (hu**@perFact.R EMOVETHIS.info. INVALID) writes:
Assuming that the IDENTITY function works as advertised, you'll never be
able to get a situation with MIN(id) other than 1 and MAX(id) other than
1000 (assuming the COUNT(*) check is passed).
It's difficult to say what is advertised. We know that you should not
reply on ORDER BY. Here is a TOP, that I don't really whether I can trust.
The paranoid check is cheap. The cost for an unexpected result is expensive.
--
Erland Sommarskog, SQL Server MVP, es****@sommarsk og.se
Books Online for SQL Server 2005 at http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at http://www.microsoft.com/sql/prodinf...ons/books.mspx This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics |
by: Eugene |
last post by:
I have a table EugeneTest(id_num, fname, minit, lname)
where field "id_num" is type IDENTITY and has UNIQUE constraint
Let's say 2 user execute this code at the same time:
DECLARE @return integer
use EugeneTest
INSERT employees
( fname, minit, lname)
VALUES
|
by: Abraham Andres Luna |
last post by:
hello everyone,
does anyone know why i can't access the
HttpContext.Current.User.Identity.Name property in a .cs file?
this is the index.aspx page:
<%@ Page Language="C#" %>
<script runat="server">
void Page_Load(Object Sender, EventArgs E)
{
|
by: Rob |
last post by:
Hi all,
I have a bit of a complicated question, hope we have an SQL guru out there
that can help us solve this killer problem.
Due to the size of SQL Database we have (largest in the US), we try to
pre-process large data files in IO until we are ready to insert directly into
the database via BCP (quick, no constraints, no mess... well um that's the
problem)
|
by: thermate |
last post by:
http://www.latimes.com/news/nationworld/nation/la-na-libby26jan26,1,2730931.story?coll=la-headlines-nation
Aide testifies Cheney helped effort to discredit Wilson
By Richard B. Schmitt, Times Staff Writer
5:27 PM PST, January 25, 2007
WASHINGTON -- Vice President Dick Cheney and his former chief of staff,
I. Lewis "Scooter" Libby, were personally and actively involved in an
effort to spin news coverage and discredit a critic of the...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth.
The Art of Business Website Design
Your website is...
| |
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own....
Now, this would greatly impact the work of software developers. The idea...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules.
He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms.
Adolph will...
|
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one.
At the time of converting from word file to html my equations which are in the word document file was convert into image.
Globals.ThisAddIn.Application.ActiveDocument.Select();...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols.
I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
| |
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
|
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
| |