September 16, 2013 at 8:32 pm
CREATE TABLE #Total_Count
(NUM_DEATHS_1870 numeric (8,0), NUM_DEATHS_1880 numeric(8,0))
INSERT INTO #Total_Count (NUM_DEATHS_1870)
(SELECT COUNT (*) COD
FROM [dbo].[1870_1880_DAT]
WHERE YR_Died = '1870')
INSERT INTO #Total_Count (NUM_DEATHS_1880)
(SELECT COUNT (*) COD
FROM [dbo].[1870_1880_DAT]
WHERE YR_Died = 1880)
MY OUTPUT
NUM_DEATHS_1870 NUM_DEATHS_1880
612 NULL
NULL 720
September 16, 2013 at 8:56 pm
Your posted DLL is not complete. So nobody can run your code to see how it works. Creation and insert statements are needed for [dbo].[1870_1880_DAT]
How do you remove the nulls? Don't put them there in the first place!;-)
Add NOT NULL to each column in your table definitions.
Add 0's to your insert statements where there is no data.
Use IsNull and/or coalesce to convert nulls to something else in the query.
__________________________________________________________________________________________________________
How to Post to get the most: http://www.sqlservercentral.com/articles/Best+Practices/61537/
September 16, 2013 at 10:00 pm
(NUM_DEATHS_1870 numeric (8,0) Not Null, NUM_DEATHS_1880 numeric(8,0) Not Null)
How would I add 0's to INSERT?
September 16, 2013 at 10:03 pm
CREATE TABLE #Total_Count
(NUM_DEATHS_1870 numeric (8,0) NOT NULL, NUM_DEATHS_1880 numeric(8,0) NOT NULL)
INSERT INTO #Total_Count (NUM_DEATHS_1870)
(SELECT COUNT (*) COD
FROM [dbo].[1870_1880_DAT]
WHERE YR_Died = '1870')
INSERT INTO #Total_Count (NUM_DEATHS_1880)
(SELECT COUNT (*) COD
FROM [dbo].[1870_1880_DAT]
WHERE YR_Died = 1880)
Here's my output
(1 row(s) affected)
(1 row(s) affected)
Msg 515, Level 16, State 2, Line 21
Cannot insert the value NULL into column 'NUM_DEATHS_1880', table 'tempdb.dbo.#Total_Count________________________________________________________________________________________________________000000000014'; column does not allow nulls. INSERT fails.
The statement has been terminated.
Msg 515, Level 16, State 2, Line 28
Cannot insert the value NULL into column 'NUM_DEATHS_1870', table 'tempdb.dbo.#Total_Count________________________________________________________________________________________________________000000000014'; column does not allow nulls. INSERT fails.
The statement has been terminated.
(0 row(s) affected)
September 16, 2013 at 10:32 pm
INSERT INTO #Total_Count (NUM_DEATHS_1870, NUM_DEATHS_1880)
(SELECT COUNT (*), 0
FROM [dbo].[1870_1880_DAT]
WHERE YR_Died = '1870')
INSERT INTO #Total_Count (NUM_DEATHS_1870, NUM_DEATHS_1880)
(SELECT 0, COUNT (*)
FROM [dbo].[1870_1880_DAT]
WHERE YR_Died = 1880)
__________________________________________________________________________________________________________
How to Post to get the most: http://www.sqlservercentral.com/articles/Best+Practices/61537/
September 17, 2013 at 3:27 pm
I'm learning SQL and much respect for those who program in SQL. Thank you. I learned something. My solution will paint me into a corner. My goal was to declare variables I could use throughout my program. Declaring failed to work beyond doing a routine. I was going to make temp tables and call out the value from the table using select statements. Failure is the best teacher. I get the basic stuff. I could see what your solution did. SQL moves from simple to very complex.
September 17, 2013 at 4:00 pm
Study up on constraints as well as Foreign Keys. These help keep the "bad" data out of the database regardless of where the data is coming from.
As you saw on your own example, the insert was rejected because of the NOT NULL constraint. This means that any program, not just yours, that tries to insert a data element that is NULL will be rejected!
The easy stuff in SQL is easy. The rest just takes time, study and experience and learning to think about problems in a completely different way.
__________________________________________________________________________________________________________
How to Post to get the most: http://www.sqlservercentral.com/articles/Best+Practices/61537/
September 17, 2013 at 6:35 pm
CREATE TABLE #Total_Count
(NUM_DEATHS_1870 numeric (8,0), NUM_DEATHS_1880 numeric(8,0))
INSERT INTO #Total_Count (NUM_DEATHS_1870, NUM_DEATHS_1880)
(SELECT COUNT (*), 0
FROM [dbo].[1870_1880_DAT]
WHERE YR_Died = '1870')
INSERT INTO #Total_Count (NUM_DEATHS_1870, NUM_DEATHS_1880)
(SELECT 0, COUNT (*)
FROM [dbo].[1870_1880_DAT]
WHERE YR_Died = 1880)
SELECT *
FROM #Total_Count
6120
0720
_____________________________
What I was trying for was
612 720
September 17, 2013 at 7:09 pm
What I was trying for was
612 720
Now we get to the crux of the issue. The null thing was a red herring. It had nothing to do with what you were actually looking for. It did not matter if there was a null or 0. You have a formatting question.
So for future posts, please read this: http://www.sqlservercentral.com/articles/Best+Practices/61537/
Here is what your post should look like:
declare @Total_Count table
(
NUM_DEATHS_1870 numeric(8,0) NOT NULL
, NUM_DEATHS_1880 numeric(8,0) NOT NULL
)
declare @1870_1880_DAT table
(
Yr_Died int
)
Insert @1870_1880_DAT (yr_died) values
(1870),
(1880),
(1880),
(1870),
(1880)
To populate the total_count table, you are in essence de-normalizing your 1870_1880 table. If that is what you really want to do, then this should do it:
Insert into @Total_Count (NUM_DEATHS_1870, NUM_DEATHS_1880)
(Select
(Select COUNT(*) from @1870_1880_DAT where Yr_Died = 1870),
(Select COUNT(*) from @1870_1880_DAT where Yr_Died = 1880))
SELECT * from @Total_Count
As you can see, this query is a maintenance problem. You should have a table called Deaths with the column Yr_Died. The table can be populated with any year. A different table does not need to be created (or should be) for different ranges of years. You can create queries that require minimal maintenance like this:
select yr_died, COUNT(*) Total
from @1870_1880_DAT
group by Yr_Died
order by Yr_Died desc
But of course, I am just speculating here based on what has been posted. I could be way off base, but the basic principles still hold true.
__________________________________________________________________________________________________________
How to Post to get the most: http://www.sqlservercentral.com/articles/Best+Practices/61537/
September 17, 2013 at 7:28 pm
...snipped...
As you can see, this query is a maintenance problem. You should have a table called Deaths with the column Yr_Died. The table can be populated with any year. A different table does not need to be created (or should be) for different ranges of years. You can create queries that require minimal maintenance like this:
select yr_died, COUNT(*) Total
from @1870_1880_DAT
group by Yr_Died
order by Yr_Died desc
But of course, I am just speculating here based on what has been posted. I could be way off base, but the basic principles still hold true.
This would definitely better a better and more maintainable approach. As the data population grows into other years, then this query would work WITHOUT modification; other would require modification for every year data was added.
Having a mind for data (set-based operations) is helpful here...
September 18, 2013 at 2:52 pm
PIVOT -
SELECT [1870], [1880]
FROM
(select yr_died
from @1870_1880_DAT ) AS SourceTable
--GROUP BY EJTranID, BranchID
PIVOT
(
Count(yr_died)
FOR yr_died IN ( [1870], [1880])
) AS PivotTable;
September 18, 2013 at 6:42 pm
There's alway that smart ***:-) I'm impressed by your knowledge of SQL. I didn't know this option existed. Honestly much of your code and the logic of your solution escapes me. It's fragments, like this this, that suggest to me there is so much to learn. I also, find myself intimidated. I have been able you learn just enough SQL to appreciate the immensity of knowledge and creativity you and others have of SQL. Thanks both for your solution and for enticing me intellectually. May what you have given me will become part of a Rosetta that will aid me in understanding SQL.
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply