Column Count Percentage Of Not Null Fields

Jul 27, 2006

Hello folks,

I am stuck at a problem, not sure on how to go about writing a query that will return as a percentage the number of fields in a row that are null.

For instance, a row from my table:
Row1 : field1 field2 field3

If field3 is empty or null, my query should return 67%.

So far I have gotten the number of fields:
select count(1) from information_schema.columns where table_name='myTable'

I could loop through the fields but I am sure there is a simpler way of doing it, I have seen something simpler in the past with some builtin SQL functions. I am using MS SQL 2005.

Thanks for your help
Mike

View 2 Replies


ADVERTISEMENT

How Do Make Sure If Column Is Null Before I Count It? ( Use SQL 2000)

Apr 29, 2008

 the Table columns is like this NO          ProductNo              Area            In               Out1               0001                     US             NULL          NULL2               0002                     UK             NULL          Y3               0003                     FR              Y                NULL 4               0004                     FR              Y                NULL5               0005                     UK              Y                NULL I have Query get the result belowArea       Count         In&OutUS           1UK           2FR           2 the Area is Group By by Area and the Count columns is counting how many recoreds Table for each Areathe problem is the column "In & OUt'I have to make sure if the "In" or "Out" is  Null , if one of them is  Null the plus 1 so the result would like  I have Query get the result belowArea       Count         In&OutUS           1                 1
UK           2                 1
FR           2                  0 which syntax I can use for the problem? I just think maybe I can use IsNULL?but I have no idea how to wirte a query ...can you give me a hint? thank you   

View 4 Replies View Related

Count The Number Of Null Values In A Column

Feb 3, 2008

Hi,

I have a table employee with 4 columns,

empno fname lname deptno

1 abc def 10
2 fff hhh 20
3 abc def NULL
4 abc def NULL
5 abc def 50

suppose i want to know the total number of null values in a particular column say deptno how shuld i write a query?

select count(deptno) from employee
where deptno IS NULL..

When i query this i get the result as 0..

View 8 Replies View Related

Record Count Percentage

Jul 19, 2007

How do I write a query to give me the total records in databaseA.tableA divided by the total records in databaseB.tableA
This is what I have so far:DECLARE @today as datetime
DECLARE @past as datetime

/* set the dates */
set @today = getdate()
set @past = getdate() - 3 /* Go back 3 days */


/* create temp table for holding total cases*/
CREATE TABLE #CaseTotalCount
(
TotalCount int
)


/**/
CREATE TABLE #FlashCycleCount
(
FlashCount int
)

/**/

INSERT INTO #CaseTotalCount (TotalCount)
SELECT COUNT (DataBaseA.dbo.tblProcedureData.CaseID) as TotalCount
FROM DataBaseA.dbo.tblProcedureData
WHERE DataBaseA.dbo.tblProcedureData.Date < @today AND DataBaseA.dbo.tblProcedureData.Date > @past



/**/
INSERT INTO #FlashCycleCount (FlashCount)
SELECT COUNT (DataBaseB.dbo.tblFlashLog.FlashLogID) AS FlashCount
FROM DataBaseB.dbo.tblFlashLog
WHERE DataBaseB.dbo.tblFlashLog.LoadDate < @today AND DataBaseB.dbo.tblFlashLog.LoadDate > @past




/*function call*/
SELECT dbo.percentage(#FlashCycleCount.FlashCount, #PeriOpTotalCount.TotalCount)
FROM #FlashCycleCount
JOIN #PeriopTotalCount
ON #PeriOpTotalCount.TotalCount != 0 This works but when I try to bind this to a formview a get nothing. any ides? 

View 6 Replies View Related

Count And Percentage Of Skill Ratings

Jul 21, 2013

I have a program which brings in Skill ratings for an assesmnet people with the title of "Worker have to take aolng with a file number they are assigned. The program also being in the reporting line that each worker is a part of.

SELECT distinct
o.VP,
o.AVP,
o.Director,
o.Supervisor,
o.Worker,
bs.File_NBR,

[Code] ....

The rating levels are 1 through 5. What I need to do is to do is create a table that shows the count and percentage of each rating giving to the workers for each skill grouped by Vp, AVP, Supervisor and Director. So all the works that are Ultimately under a AVP and all the wokers that are Ultimately under a director and so on.

NameRoleSkillCount of % of Count of % of
Rating 1 Rating 1 Rating 2 Rating 2
Gerald VPA1100 29% 13033%
Gerald VPB195 28% 9524%
Gerald VPB2120 35% 7018%
Gerald VPB330 9% 10025%
KimVPA1
KimVPB1
KimVPB2AND SO ON

[Code] ....

View 1 Replies View Related

SQL Server 2008 :: Percentage Of Fields Used In A Table - Query?

Oct 28, 2015

I have tableX with columns colA, colB, colC, colD and there are 2256 rows in the tableX.

I would like to find out the percentages of colA, colB, colC, colD that hold data (where it is not an empty string or NULL value).

So out of 2256 rows in the table, the user has stored data in colA 1987 times, colB 2250 times, colC 2256 times and colD 17 times.

So the report would say:

colA: 88.07%
colB: 99.73%
colC: 100%
colD: 0.01%

We have an application that has a bunch of fields that we believe are not being used and would like to remove them, but we need to prove this by looking at the data.

I know I could run a query, one at a time and change the column name, but this would take a long time as there are a lot of columns in this table. I am hoping there is some way to do this in one query.

View 2 Replies View Related

Subreports: Parameter Value Dropdown Shows Sum And Count Fields But Not The Actual Data Fields.

Jan 28, 2008


I have just started using SQL Server reporting services and am stuck with creating subreports.

I have a added a sub report to the main report. When I right click on the sub report, go to properties -> Parameters, and click on the dropdown for Parameter Value, I see all Sum and Count fields but not the data fields.

For example, In the dropdownlist for the Parameter value, I see Sum(Fields!TASK_ID.Value, "AppTest"), Count(Fields!TASK_NAME.Value, "CammpTest") but not Fields!TASK_NAME.Value, Fields!TASK_ID.Value which are the fields retrieved from the dataset assigned to the subreport.

When I manually change the parameter value to Fields!TASK_ID.Value, and try to preview the report, I get Error: Subreport could not be shown. I have no idea what the underlying issue is but am guessing that it's because the field - Fields!TASK_ID.Value is not in the dropdown but am trying to link the main report and sub report with this field.

Am I missing something here? Any help is appreciated.

Thanks,
Sirisha

View 3 Replies View Related

DB Engine :: Fragmentation Percentage - Page Count Is 300 Only

Jan 22, 2015

What is the impact of fragmentation when my tables fragmentation is 99% but page count is 300 only?

View 6 Replies View Related

SQL Server 2012 :: Calculation Based On Count Function To Format As Decimal Value Or Percentage

Dec 4, 2014

I'm trying to get a calculation based on count(*) to format as a decimal value or percentage.

I keep getting 0s for the solution_rejected_percent column. How can I format this like 0.50 (for 50%)?

select mi.id, count(*) as cnt,
count(*) + 1 as cntplusone,
cast(count(*) / (count(*) + 1) as numeric(10,2)) as solution_rejected_percent
from metric_instance mi
INNER JOIN incident i
on i.number = mi.id
WHERE mi.definition = 'Solution Rejected'
AND i.state = 'Closed'
group by mi.id

id cnt cntplusone solution_rejected_percent
-------------------------------------------------- ----------- ----------- ---------------------------------------
INC011256 1 2 0.00
INC011290 1 2 0.00
INC011291 1 2 0.00
INC011522 1 2 0.00
INC011799 2 3 0.00

View 5 Replies View Related

Percentage Column AND Row

Jun 22, 2007

Hello,



I have a matrix that looks as follows:



Name Jan Feb Total

John 5 6 11

Mary 3 4 7

Total 8 10 18



I want to add a percent column to the RIGHT of the total, and also on the bottom row. I can't find any clear examples of how to do this. If I had a new column, it adds additional headers beneath my top row. Or, my columns appear to the LEFT of the data, not the right. Can some please post some simple instructions that will make my simple matrix look like this:



Name Jan Feb Total %

John 5 6 11 60%

Mary 3 4 7 40%

Total 8 10 18 100%

% 40% 60% 100% 100%



I am so stuck on this I can pull my hair out.



Thanks!



Michael



p.s. I really hope the next version of SSRS has a simple "Sub-total %" option that you can enable just like the sub-total column.

View 3 Replies View Related

Percentage Calculation Of Column

Aug 27, 2013

I have a column Bill_amount from bill_details table , i want to find 100%,30% of that column.

View 2 Replies View Related

Percentage Column In Matrix

Jan 26, 2007

Hi,
I would like to generate following table with reporting service matrix.
But if I use the expression : percentage coulmn=sum(field!qty.value) / sum(field!qty.value, "region_group")
It will become a percentage of a row total, instead of a group total (store group). Please see the second table.
Any ideas?

Thank you!

(Correct!)
store1 store2 level1 level2 level3 sub total level1 level2 subtotal category region qty % qty % qty % qty % qty % qty % qty % C1 APAC 10 10% 20 20% 70 70% 100 100% 25 50% 25 50% 50 100% EURP C2 CHINA


(Wrong) store1 store2 level1 level2 level3 sub total level1 level2 subtotal category region qty % qty % qty % qty % qty % qty % qty % C1 APAC 10 6% 20 13% 70 46% 100 25 16% 25 16% 50 EURP C2 CHINA

View 1 Replies View Related

Update Column With Percentage Of Total For Each Row

Dec 9, 2011

I'm trying to work out the value of a item as a % of the group.Using my simplified table below (table called sweetie)

Date Name Sweets
01/11/2011Pete5
01/11/2011Paul10
01/11/2011Paddy15
02/11/2011Pete2
02/11/2011Paul4
02/11/2011Paddy6

I would like to select a column called PERC, which would be the sum of Sweets/sum of Sweets(grouped by date).So for example for the first value I would have 5/(5+10+15).I can write the query so it totals all the sweets, but I want to use totals by date and am unsure how I would do this..My query so far is (but this doesn't use totals by date)

Date NameSweetsPERC
01/11/2011Pete5 0.12
01/11/2011Paul10 0.24
01/11/2011Paddy15 0.36
02/11/2011Pete2 0.05
02/11/2011Paul4 0.10
02/11/2011Paddy6 0.14

How can I get it to calculate based on the sum by date. Would I need to declare a variable for each date, that variable being the sum of sweets?

View 4 Replies View Related

How Do I Add A Percentage Column After The Total On A Matrix?

Mar 27, 2007

Hi. I am looking to create a Matrix-based Report in SQL Server 2005 Reporting Services. I have my query, and a dynamic number of both rows and columns. I have Totals on the "bottom" and "right".

What I'd like to do is add a Percentage column to the right of the right-based Totals column. So the columns would read Col1, Col2, .., ColN, Total, Percentage.

Is this possible???

Thanks.

View 12 Replies View Related

Transact SQL :: Add Total Column And Percentage

May 31, 2015

How can I add a Total Column as the last column in this query and also add a total column to the bottom row of the query?  Then after the total column on the right, add a % column.  So my expected returned set would be like so:

Location
       Train
         Bat 
         Ball
     Nuggets
       Total
      %

[code]..

View 12 Replies View Related

T-SQL (SS2K8) :: How To Find And Add Column Of Percentage In Table

Mar 16, 2015

i made a procedure to the below output now i need to add column percentage depends on these output

block Response Heads Dept Actuals
1Sales Sales01.Sales (net of Sales Tax)1087.5999999999999
1Finance Sales02.LESS:-EXCISE DUTY 22.800000000000001
1Sales Sales03.Net Sales 1064.8
2HR HR 04.Personnel Cost 170.60000000000002
3Materials & productionCOGS 05.Material Cost 376.70000000000005

(.i.e)

block Response Heads Dept Actuals percentage
2HR HR 04.Personnel Cost 170.60 170.60*100/1064.8(dept=03.Net
Sales)
3Materials & productionCOGS 05.Material Cost 376.70 376.70*100/1064.8(dept=03.Net
Sales)

How to write make a query for that?

View 4 Replies View Related

SQL Server 2012 :: Best Way To Handle Like Percentage On Column Too Large For Index

Sep 18, 2015

We have a table to 100M rows and up until now we were fine with an non clustered index a varchar(4000) because we never went above 900 bytes (yes it is a bad design).We have the need to support international character sets now so the column was updated to nvarchar(4000) and now we have data past the 900 byte limit.

The data is long, seems useless but is needed by the business and they need to be able to search "where bigcolumn like 'test%'". With an index, even with a huge amount of data, it was 'fast'. Now of course without an index it is unusable. The wildcard is always at the end of the search. I made a full text index on the column and basic queries such as: select * from ourtable where contains(bigcolumn, 'AReallyLongStringofTextHere') works fine unless there is a space in the data. We loose thousands of returned rows because of spaces in the data.

I have tried select * from ourtable where contains(bigcolumn, '"AReallyLongStringofTextHere that includes spaces"') but not all of the data is returned. I get 112 rows with the contains statement. The table scanning statement of "select * from ourtable where bigcolumn like 'AReallyLongStringofTextHere that includes spaces%' returns 1939 rows.I understand that a full text index is breaking the long string up since it contains spaces. Is there a way to retain the entire string as 1 index entry or is there a way to fix my query to return all of the rows?

View 9 Replies View Related

Reporting Services :: Calculating Percentage Of Column Total In Charts

Jul 22, 2015

Calculating % of Column Total in Charts...

Doing this exercise in Excel is always very simple: 

View 3 Replies View Related

Is There Any Solution For Dispalying String And Percentage Values In Single Column

Dec 1, 2007

i have a report with contains preview of percentage columns example of percentage of student marks in perticular subject like 95%. and if suppose any student not attend any test i have to dispaly like not attended statement.



so i have display two fields like 95% and not attended statement in same column, i given Cstr(Fields!Data.Value), it gives two fields with contains not attended statement of perticular query and it dispalys 0.95 % . but i need 95% and not attended statement for perticular query in same column.


is there any solution for dispalying string and percentage values in single column for given perticular query and those two values are disply same result compare with preview at the time of export to excel sheet



plese send solutions ASAP

Thanks

James vs

View 1 Replies View Related

Count For 2 Fields

Jul 30, 2014

I have a table named pop. This table has 2 fields, field codes with articles and other field with details of those articles. I intend to make a count to items that have more than 1 detail. In addition to this result is possible result for example:

Detailarticles
64
535
4111
3523
22207

the first line says I have 4 articles with 6 details.

View 16 Replies View Related

How Do You SUM The Fields Retrieved By COUNT?

Mar 15, 2004

I am trying to do this in MS Access actually but it is an SQL question.

This query:

SELECT LoanNo, COUNT(LoanNo) AS 'Count'
FROM DedupTest031504
GROUP BY LoanNo
HAVING COUNT(LoanNo) > 1;


returns:
LoanNo Count
46690128 2
46861821 2
47762138 3
47762154 3
48257239 2
48257663 2
48257719 2
48258143 2
48258167 2

which is correct but how do you SUM the COUNT field? In other words, I want the total number of duplicate records in the table. Is there another way alltogether?

I tried:

SELECT LoanNo, SUM(COUNT(LoanNo))
FROM DedupTest031504
GROUP BY LoanNo
HAVING COUNT(LoanNo) > 1;

but I get a "CANNOT HAVE AGGREGATE FUNCTION IN 'SUM(COUNT(LoanNo))' error.

Thanks.

ddave

View 12 Replies View Related

Using COUNT To Add Fields In A Report

Apr 21, 2004

If I am totaling fields by groups of rows and I do so for every group do I need to use a stored procedure or cursor for this? I don't have a lot of experience with these areas but will give it a go based on what I find out.

Let me try to provide an example.

BranchNo OrderNo ErrorCode1 ErrorCode2 ErrorCode3
478 111 0 1 1
478 112 0 0 0
478 113 1 0 0
610 119 0 0 0
610 120 1 0 0

I am trying to total the "error code" fields for each Branch. Of course, some don't have any, some have multiple errors. If a stored procedure is the only way, it will be a problem as our company's DBA has not given me permissions to run SPROCs. Is there a way to do this in a query?

I have been trying to figure out a subquery for this and it is not working.

ddave

View 14 Replies View Related

Getting Count With Multiple Fields

Sep 6, 2005

Hi all,I'm running into a road block, and I know I've done this before. I'mgetting fields from two tables, and I need to do a count of similaritems with it showing some extra info.Here's my fields:Log.LogId - IntLog.LogDispatcherID - IntOfficer.OfficerID - IntOfficer.OfficerFirstName - VarcharOfficer.OfficerLastName - VarcharI can get the info I need without a count with this:select a.LogID,a.LogDispatcherID,b.OfficerFirstname + ' ' + b.OfficerLastname as OfficerNamefrom[Log] a, Officer bwhere a.LogAssigned1 = b.OfficerIDBut when I try to add a count and group-by it errors out:select Count(a.LogID) as LogCount,a.LogDispatcherID,b.OfficerFirstname + ' ' + b.OfficerLastname as OfficerNamefrom[Log] a, Officer bwhere a.LogAssigned1 = b.OfficerIDGroup By a.LogIDI've done this before, but this isn't working. It's giving the error"it is not contained in either an aggregate function or the GROUP BYclause" for each field other then LogID.How can I do this? I want output similar to this:LogCountLogDispatchIDOfficerName334Tom Jones422John Smith.... EtcThanks for any suggestions or ideas...Sam Alex

View 2 Replies View Related

How Do I Exclude Null Fields?

Apr 28, 2006

Is there a way to write a select statement that will pull only fields that are not null?

View 2 Replies View Related

How Do You Query To Get All Fields With A Null Value?

Jun 6, 2001

how can i write a SQL query that will pull all records that are equal to NULL??

View 1 Replies View Related

Programming With Null Fields

Apr 9, 2000

I am working on Sql Server 7.0 with a group of Visual Basic programmers that have made the following request:

1.They want all decimals/integer fields in the database to default to 0(zero).
Is there an way to do this for all fields instead of on an individual basis?
2.They don't want a null returned on any character string field.

What is the best way to take care of these 2 requests? Thanks in advance.
Karen Suenram

View 1 Replies View Related

Ignore Null Fields

Sep 29, 2005

Hi,

I have a query like this one

SELECT expense_id, CAST(expense_id AS char(10)) + ' - ' + CAST(trip_km AS char(5))+ ' - ' + CAST(expense_amount AS char(5)) + ' - ' + charge_centre AS ExpenseDesc

If charge center is null, I need to ignore this field. How can I achieve this? The reason is that if any of the field is null, it will return ExpenseDesc as null.

Thanks

View 1 Replies View Related

Select When Some Fields Are Null...

Jun 5, 2008

Here is the problem that I am struggling with. The structure is:

States: StateID, StateName
Counties: CountyID, CountyName
Cities: CityID, CityName
Zips: Zip, StateID, CountyID, CityID
Regions: RegionID, RegionName
Region_Data: RegionID, StateID, CountyID, CityID

The thing about the region is that it can be defined by states only, in which case CountyID and CityId are NULL, can be defined by Counties too, in which case only CityID is NULL or it can be defined up to City level, in which case all 3 are set to something. Example, Northeast would be all cities from northeast, but Pocono would be just some counties in PA, and so on...

The issue is now selecting all cities that belong into a region... Normally I would join the Zips table with the Region_Data table and retrieve all CityIDs... The issue is that, as I said, the CountyID and CityID might be null for some records, so I am not sure how to retrieve them?

I came up with one idea in which I create 3 temporary tables and I select in them all the records that have 1) only state, 2) only state and county, 3) all 3, then I join each and I union the results...

But I am wondering if there is a way to do the select in one shot?

Any idea? I appreciate your help,

Thank you,

Iulian



Regards,

Iulian

View 3 Replies View Related

NULL On Empty Fields

Mar 16, 2008

How can I make empty cells show NULL on my table? Some cells show NULL others won't. Does this mean that they have contents?

The reason being is that, when I use the code

Select *
From Employees
Where JobDescription1 Like '%montly%'

Those with empty jobdescription1 show with the legitimate results.

Any help please?

Thanks!

View 2 Replies View Related

Get All The Fields In A Question Even If There Is A Null

Nov 20, 2007


Im trying to use MS SQL server mananagement studio to greate a view to recive information from my database.
The problem i have is that i include a tabel where a post have "Null" then that post will not be shown i my qustion/View.

How can i make soure that i get all the post even if they have "null" in a field where it do not mather(its not a Key)

Greatful for very fast, help. Need this to be solved tomorrow :-(

Mitmit

View 1 Replies View Related

Need Help With Filtering Fields With Null And More

Apr 26, 2006

Requirements:

1) Cannot have results in which both m.homephone & d.homephone are both null, or both m.workphone & d.workphone are both null in the result set

2) We also do not want to see any combos where one homephone is null and the other has only the char 0....same for workphone...no null & 0 combinations.

e.g.

m.homephone   d.homephone

null                       0

0                          null

The phone fields are varchar

I know this is hidiously wrong but is my first sloppy attempt at best:
select m.number, m.homephone as master_homephone, d.homephone as debtor_homephone, m.workphone as master_workphone, d.workphone as debtor_workphone
 
FROM master m
 
INNER JOIN debtors d ON d.Number = m.number
 
where (d.homephone <> m.homephone OR d.workphone <> m.workphone)
AND (d.homephone IS NOT NULL AND m.homephone IS NOT NULL)
AND (d.workphone IS NOT NULL AND m.workphone IS NOT NULL)
AND NOT ((d.homephone IS NULL AND m.homephone = '0') OR (d.homephone = '0' AND m.homephone IS NULL))
AND NOT ((d.workphone IS NULL AND m.workphone = '0') OR (d.workphone = '0' AND m.workphone IS NULL))

 

View 7 Replies View Related

SQL Server 2012 :: Max Count Grouped By All The Fields

Sep 15, 2015

Trying to get the max count grouped by all the fields. All the fields are the same, but trying to get the location for each physician that has the largest number of patients.

if the output for the sql below is:

101, 10, Jon, Smith, MD, Ortho, OR, 15
101, 10, Jon, Smith, MD Ortho, 1, 12
101, 10, Jon, Smith, MD, Ortho, 2, 10
24, 3, Mike, Jones, MD, Neuro, OR, 21
24, 3, Mike, Jones, MD, Neuro, 2, 43

I'd like to have the query rewritten so the results are as:

101, 10, Jon, Smith, MD, Ortho, OR, 15
24, 3, Mike, Jones, MD, Neuro, 2, 43

SELECT
a.attendingmdkey,e.[provider id],e.[first name],e.[last name],e.title,e.specialty,l.locationname,count(a.accountid) as Count
FROM accounts a
left outer join location l on l.locationid=a.locationid
left outer join providers e on e.[ProviderID]=a.attendingmdkey
where a.dischargedate>='2014-12-01' and a.dischargedate<'2015-01-01'
and a.divisioncode in ('1','2','$')

group by a.AttendingMdKey,e.[provider id],e.[first name],e.[last name],e.title,e.Specialty,l.locationname
order by a.AttendingMdKey

View 3 Replies View Related

Database Tables And NULL Fields

Nov 17, 2006

Hi  there,
 I have a fairly generic question regarding database design.
I am working  on my first database using SQL  Express.
I am testing the database by inserting records into the pre  defined tables.
Sometimes I  forget to input text into a non null  field. When
this  happens  it displays an error  and then skips a  few rows
which makes my  row numbering  messy.  For example  say I have 
Row  1, Row 2 Row  3 all  nicely  formatted but on Row 4  I make  a mistake
this  row then disapears and  their is  gap  between  3 and 5 where  4  was.
Although I am just testing, I was  wondering  what happens in a  real
scenario if an inexperienced data input person makes a few  mistakes it
makes the database  numbering  systems look bad. In  this case
is it common  to  make  most  fields  NULL to allow for admin errors??
 
 
 

View 8 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved