MS SQL Server :: Distinct Count With Null Values (grand Total)

Distinct Count With Null Values (grand Total)

May 10, 2007

Hello,

I am using SQL Server 2005. I have a DB of professors and information related with them. I created the cube, it consist of:
Measures:
Measure group Professors:
Amount of projects (COUNT proj_id)
Amount of publications (COUNT pub_id)
Amount of e_books (COUNT book_id)
--------------
Measure group Projects:
Distinct amount of projects (DISTINCT COUNT proj_id)
--------------
Measure group Publications:
Distinct amount of publications (DISTINCT COUNT pub_id)
--------------
Measure group E_books:
Distinct amount of e_books (DISTINCT COUNT book_id)
Calculated measures:
Amnt_Projects
iif ([Measures].[ Amount of projects ] = 0 OR [Measures].[ Amount of projects] = NULL,0,[Measures].[ Distinct amount of projects])
Amnt_Publications
(similar to the above one)
Amnt_E_books
(similar to the above one)
---------------------------
Dimensions:
dimPROFESSORS
- prof_id
-surname
-name
-gender
dimPROJECTS
- proj_id
-type name
-name
dimPUBLICATIONS
- pub_id
-type name
-name
dimE_BOOKS
- book_id
-name
Date_Projects
-date_id
-years
Date_Publications
-date_id
-years
Date_E_books
-date_id
-years

For example, when I browse the cube:
prof_id____Amount of projects___Distinct amount of projects___Amnt_Projects
1032------------------- 30 --------------------------1----------------1
1070-------------------90 --------------------------2----------------2
1111-------------------0 ---------------------------1----------------0
1137-------------------0 ---------------------------1----------------0
1234-------------------1404--------------------------9----------------9
1721-------------------504--------------------------7----------------7
2661-------------------85 --------------------------5----------------5
...--------------------...---------------------------...----------------...
6999------------------- 20---------------------------1-----------------1
9956-------------------50---------------------------5-----------------5
Unknown----------------(empty)---------------------(empty)-----------0
Grand Total------------ 2421------------------------11-----------------11

Grand Total �11� is the amount of distinct projects +1 (because of the unknown member). So the last column shows the right amount of projects for the professor but I want Grand Total to sum those values and show, how many projects do the professors have (it should be �59� if for all professors). How could I get the right value to be shown in Grand Total?

View 2 Replies View Related

Err On Grand Total In Cube Measure Using A Distinct Count Agregation

May 28, 2007

Hi,

I'm using sql server analysis services 2000, i have a problem with grand total when i select more than one member of any demension. the measure of this cube is built on a distinct count agregation function. i have only one measure in th cube

can some body help me !!

View 1 Replies View Related

SQL Server 2012 :: Sales Over Years - Retrieve Values In Single Query For Multiple Years And Grand Total?

Apr 24, 2015

View 6 Replies View Related

Count By Distinct Value When All Parts Not Null

Mar 12, 2007

Hello,

I'm fairly new to SQL as so am looking for help with an ad hoc query. The data in focusing on is from table named APs:

FileNo Type PartNo Completed
6 Northbound 1 03/03/2007
6 Northbound 2 NULL
6 Other 1 NULL
6 Other 2 NULL
20 Proof 0 19/07/2006
20 TCP 0 21/07/2006
24 40-Day 1 16/01/2006
24 40-Day 2 16/03/2006
24 Other 0 NULL
44 Northbound 1 16/01/2006
44 Northbound 2 16/06/2006
44 Northbound 3 16/12/2006
44 Northbound 4 01/01/2007

I've tried variations on a SELECT statement like below but have been unable to find a way to count only those types per fileNo that have all partNo completed (and to count all types per fileNo with a partNo of 0 and a completed date as they have no parts):

SELECT [FileNo], COUNT(DISTINCT [Type]) AS CountOfAPs
FROM APs
WHERE (completed IS NOT NULL)
GROUP BY [File]

Total count should be: 4

View 9 Replies View Related

How To Conditionally Count Distinct Values?

Mar 17, 2008

Here is my dataset used by my report definition. The combo of barcode and order id is unique. The 'isDiscountedItem' field indicates if the customer used a coupon to purchased an item at a lower price.

departmentId classId barcode orderId isDiscountedItem
----------------------------------------------------------------------------------------------------------------------
1 1 123 1 True
2 7 456 1 False
1 1 123 2 False
1 1 123 3 True
1 1 789 3 True
2 7 456 3 False
... ... ... ... ...

I want to group my report by department id, class id and barcode. Then, I want to count all distinct order ids for which there was at leat one discounted item.

My report would produce the following output considering the above dataset:

Merchandise Number of customers who used a coupon
--------------------------------------------------------------------------------------------------------------
Department 1 2

Class 1 2

Barcode 123 2
Barcode 789 1
Department 2 0

Class 7 0

Barcode 456 0

I've been looking at a possible solution using hash tables defined in the report code but I would like to find a 'cleaner' solution. Any help would be appreciated.

View 3 Replies View Related

DB Design :: How To Get Distinct Count Of Values

Sep 21, 2015

I have the below query,

select
a.Assignment_UniqueID as DeploymentID,
a.AssignmentName as DeploymentName,
a.StartTime as Available,
a.EnforcementDeadline as Deadline,
sn.StateName as LastEnforcementState,-----Required Column

[Code] ....

o/p: is :

LastEnforcementState NumberOfComputers PComputers

Compliant 7056 91.54
Downloading update(s) 39 0.51

[code]....

I want to add these two rows information into single row,

Failed to Download Updates(s) 131(127+8) 1.66(1.65+0.1)

how to do this in my sql query?

View 12 Replies View Related

Reporting Services :: Count Values In A Column Based Upon Distinct Values In Another Column In SharePoint List

Sep 7, 2015

We have SharePoint list which has, say, two columns. Column A and Column B.

Column A can have three values - red, blue & green.

Column B can have four values - pen, marker, pencil & highlighter.

A typical view of list can be:

Column A - Column B
red - pen
red - pencil
red - highlighter
blue - marker
blue - pencil
green - pen
green - highlighter
red - pen
blue - pencil
blue - highlighter
blue - pencil

We are looking to create a report from SharePoint List using SSRS which has following view:

red blue green
pen 2 0 1
marker 0 1 0
pencil 1 3 0
highlighter 1 1 1

We tried Sum but not able to display in single row.

View 2 Replies View Related

Grand Total

Oct 18, 2007

Hello All,
I've created a report that has a count of dates reports. Every company has at least one date report. I've group the report by company and did a count this works find. Now I want to SUM up this field and put it at the end of the report.
I've used =sum(reportitems!textbox10.value) but it only works at the page footer. And only give me a grand total for that page. I want a grand total for the whole report. Is there a report footer???

Thanks
Set_Shot_Dave

View 2 Replies View Related

SQL Server 2012 :: Add Column Names As Total And Subtotal For NULL Values?

Jan 21, 2014

How do I add column names as Total and SubTotal for NULL values.

SELECT DISTINCT
--[Group]
[Month]
,[Market]
,[Environment]
,[type]
, COUNT(*)

[code]....

View 9 Replies View Related

Get The Grand Total Of This Query

Dec 12, 2003

I need to get the Grand Total of the results of this query.
The query pulls the total customer quotes for each community in a management company and loads my DataGrid:

This query works fine for individual community totals"SELECT TOP 100 PERCENT vcName, COUNT(DISTINCT vcCustId) AS " & _
" Total FROM dbo.PropReportData WHERE cManagementCo = '" & Session("MgmtCo") & "' and " & _
" (vcEntryDate >= CONVERT(DATETIME, '" & StartDate & "', 102)) AND " & _
"(vcEntryDate <= CONVERT(DATETIME, '" & EndDate & " 11:59:59 PM" & "' , 102))GROUP BY vcName ORDER by vcName"

At first glance you would think that the following query would return the Grand Total for all communities in the management company:

Current Grand Total Query"SELECT COUNT(DISTINCT vcCustID) As gTotal FROM PropReportData WHERE
cManagementCo = '" & Session("MgmtCo") & "' AND vcEntryDate >= '" & Session
("StartDate") & "' AND vcEntryDate <= '" & Session("EndDate") & " 11:59:59 PM' "

But here's the problem. If there are multiple customer quotes created for different communites, then the customer(vcCustID) is only counted once in the Grand Total Query because I have to use DISTINCT, which of course only picks up one instance of the customer.

Thanks in advance.

View 2 Replies View Related

Grand Total Needed

Jan 18, 2007

I have two subreports (Revenue and Expense) that each have subtotals.

This works just fine, but I need a Grand Total that would sum the two subtotals.

How would I go about doing this?

So it looks something like this:

Revenue Report (matrix)

Subtotal

Expense Report (matrix)

Subtotal

<~~~~ Need Grand Total here.

View 18 Replies View Related

How To Arrive At Grand Total?

Mar 6, 2007

Hi All,

My doubt is, how to get gand total in SQL Reporting Services2005 for the following scenario?

Sample report content:

CustomerId Product Name Price

123 Apple $100.00

Orange $77.00

SUB TOTAL $177.00

124 Apple $105.00

Orange $70.00

Olive $450.00

SUB TOTAL $625.00 // sum(Fields!Price)

GRAND TOTAL $802.00 //How to get Grand total? please..

Regards,

Pragash

View 6 Replies View Related

Grand Total Problem

Aug 5, 2007

Hi,

I am trying to calculate grand totals in the table footer area on a single table report. The report table has a grouping with a filter. I want the grand total to sum the visible grouping data but instead it sums the entire dataset. I've tried summing the textbox but RS does not allow that from the footer. I've tried specified the group for the scope but apparently RS scope works outward not inward in regards to groups. A simple example of my problem might be a list of bank accounts whose balance is overdrawn. In the report you want to show only the accounts whose balance has gone negative and all the transactions for those accounts. You can create a group on the account field and then a filter on the group to sum the transaction amount and filter out the accounts whose sum is not below zero. The grand total would supposedly sum the accounts whose balance is negative. Unfortunately the sum is on the entire dataset and therefore includes accounts with good balances. I'm sure some will suggest changing the SQL but that is not an option for me. These seems like a very basic operation for a report writer so I'm guessing there is some easy way to do this that has eluded me. Thanks for any help.

View 15 Replies View Related

How To Get Null Values When Using Count

Nov 4, 2013

I have this bit of code below and would like to bring back stat_code's which have no value as 0...

select stat_code, COUNT(stat_code)AS Stat_Count
--INTO tableSroStatABD
from fs_sro
where sro_stat ='O' and whse = 'ABD'
group by stat_code
having stat_code in ('1 QUOTE WN', '2 PR & DEF', '2.1 PRTORD', '3 PRTS AVL', '4 SCHEDULD', '5 LABB JBC', '6 CSTB JBC', '7 WRKS CMP', '8 CSH APP', '9 TO B INV')
order by stat_code asc

View 5 Replies View Related

Calculate Grand Total Using Pivot

Apr 25, 2014

How to calculate grand total in sql server using pivot?

View 1 Replies View Related

Combined Orders Grand Total?

Apr 20, 2007

Hi,

Im using Access and have created a query which adds up the grand totals of clients orders. However i want to be able to add up what each client has spent in total over any given time period. Basically in SQL logic terms:

take all order totals from same client and add together order totals to produce a grand client spend total.

Anyone help!?

thanks

View 3 Replies View Related

Group Subtotal And Grand Total

Jun 12, 2007

how to add group subtotal and grand total in report? i try to add formula Sum(Field!Net_Weight.Value) in group footer and unable repeat footer on each page, it return same total on every pages. I hope to get subtotal on each page by group. the expected result would be like this:

Page1

1.Group 1

Date
D/no
Net Weight

6/1/2007
A00000100
10.45

6/1/2007
A00000101
10.95

6/1/2007
A00000102
11.45

6/1/2007
A00000103
11.95

6/1/2007
A00000104
12.45

6/1/2007
A00000105
12.95

Subtotal
70.2

Page 2

Date
D/no
Net Weight

6/1/2007
A00000100
20.15

6/1/2007
A00000101
20.25

6/1/2007
A00000102
20.35

6/1/2007
A00000103
20.45

6/1/2007
A00000104
12.45

6/1/2007
A00000105
12.95

Subtotal
106.6

Grand Total=
176.8

2.Group 2

Date
D/no
Net Weight

6/1/2007
A00000100
10.45

6/1/2007
A00000101
10.95

6/1/2007
A00000102
11.45

6/1/2007
A00000103
11.95

6/1/2007
A00000104
12

6/1/2007
A00000105
12.95

Subtotal
69.75
anybody know how to do it?

View 3 Replies View Related

Calculating Grand Total And Percentage

Oct 19, 2007

Hi All,

I have to create a table like this using SSRS.

LoanStatus NoOfLoans Amouunt Percentage

Started
1,000
1,000,000.00
100%

Sent to Partner
350
350,000.00
35%

Denied by Partner
50
50,000.00
5%

Accepted by Partner
300
30,000.00
30%

Loan Apps Pnd Disb
10
100,000.00
1%

Loan Apps Disbursed
290
290,000.00
29%

Table is grouped by loan status.
Percentage is calculate as NoOfLoans/TotalNoOfLoans.

Please can anyone tell me how to calculate the grand total and Percentages?

View 5 Replies View Related

SQL Statement Count NULL Values

Jul 20, 2005

Hello,Thanks for helping me with this... I really appreciate it.I have a table called tblPatientDemographics with a number of columns.I would like to count the number of NULL values per record within mytable.tblPatientDemographicsPatientID Age Weight Height Race1234567 20 155 <NULL> Caucasian8912345 21 <NULL> <NULL> <NULL>In the first example above I want to display '1'In the second example above I want to display '3'Any help would be very much appreciated.Thanks !Chad*** Sent via Developersdex http://www.developersdex.com ***Don't just participate in USENET...get rewarded for it!

View 3 Replies View Related

Count Of Null Values In Columns

Mar 16, 2006

Hi,

I would like to ask how can I get count of null values in each column together?

I know I can use COUNT(*) - COUNT(<col>), but I need to explicitly name every column in the query.... is it possible without it?

View 9 Replies View Related

Add Grand Total At Bottom Of Pivot Table?

Nov 9, 2013

I've been messing around with ROLLUP but have got nowhere,I have this:

DECLARE @sql NVARCHAR(MAX)
DECLARE @pivotsql VARCHAR(MAX)
DECLARE @columns VARCHAR(MAX)
DECLARE @selectlist VARCHAR(MAX)

[code]...

which works a treat - I just need a grand total at the bottom for each column.

View 7 Replies View Related

Analysis :: Custom Grand Total In SSAS?

Jul 23, 2015

I have a requirement where I need to show the maximum value in grand totals but for the dimension members the same measure has to sum.

For ex: lets say I have a measure called Test and this is a base measure. The aggregation type set to this is SUM.

For this same measure the grand totals should not show the sum instead it should show the maximum value of the dimension members which is being analyzed across.

View 4 Replies View Related

Transact SQL :: Add A Grand Total To Pivot Output?

Oct 9, 2015

I have a SP that will generate a pivot output. I want to add a grand total at the end row to sum up the counts for each column.

the SP is as below :

/* COLUMN HEADERS*/
DECLARE
@columnHeaders NVARCHAR (MAX)
SELECT
@columnHeaders = COALESCE ( (@columnHeaders) + ',[' + [Date] + ']', '[' + [Date] + ']')

[code]....

I am getting the below error:

Invalid column name 'Grand Total'.

Msg 205, Level 16, State 1, Line 16

All queries combined using a UNION, INTERSECT or EXCEPT operator must have an equal number of expressions in their target lists.

View 10 Replies View Related

Grand Total Is Showing Wrong Result

May 19, 2008

My FactEmployeeTable showing 13 rows of data related to employees, but i used a bridge table for connecting project and employee dimensions. In the ProjEmpBridge table i mapped only 6 employees data to the proj's data. When dragging projet name and employee name it is showing data related to 6 employees but grand total showing 13 as the result. I dont know why it is showing 13 instead of 6. Can anyone please solve this issue.

View 10 Replies View Related

Matrix Report Columns Grand Total

Sep 10, 2007

Hi There i have a Report That Display's data in following format

Areas
Code | Descirption | Emirates | GCC | ASIAN | OTHER ARABS | so on..
1 What ever 3 0 2 1
2 ABC 0 0 1 0
-------------------------------------------------------------------------------------------
Total 3 0 3 1

The Areas are Matric Columns and are dynamically generated .

i dont want sub total of each row , but i want Grand total at the end of the all columns generated by matrix

I have no clue how do to it

Kind Regards

View 7 Replies View Related

Grand Total Column In Matrix Report

Jan 15, 2008

Hi,
Hi All,

I am having some trouble getting a Grand Total column at the end of my matrix report (not row at the bottom), such as you get by default in an Excel pivot table.

I have managed so far to add another column that sums up all values across the data range for a particular product code. My intention was to hide all but the last (I did something similar on rows). However you cannot use an expression on the width property for columns, so this will not work.

My report is very standard:
Year
Month (grouped by year)
Product code | Description [qty]

I want a column that displays the total qty across all dates for each row. Surely this is possible?

Any suggestions would be appreciated! Thanks.

View 6 Replies View Related

Joined Tables - Count And Null Values

Jan 11, 2014

I joined different tables and got a result like this:

result | process | goal | date |
------- ---------- ------ -----------
ok | process4 | 1 | 12.10.2013
bad | process1 | 2 | 13.10.2013
ok | process1 | 4 | 12.12.2013
good | process4 | 1 | 03.01.2014
ok | process1 | 3 | 10.04.2013
bad | process3 | 6 | 09.01.2014
bad | process4 | 3 | 30.12.2013
best |NULL| NULL

Now I want to count the results by counting the processes and group them by the result.

But it should be count the latest result per process only, e.g. for goal "1" just "good" at 03.01.2014. I solved that with a subquery (date=SELECT MAX(...)..).

But now the result "best" disappears, because that column has no date.

Secondly I want to count results for a specific process, e.g. for process4. Every goal has max. one process, with different dates. But one process could have more than one goal.

I want to have this result for process4:

count | result
------ -------
1 | good
1 | bad
0 | ok
0 | best

But I got only:

count | result
------ -------
1 | good
1 | bad

I have tried a lot, but nothing works.

The whole result (best, good, ok, bad) are stored in an other table and I joined it.

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

Adding Grand Total To A Column Group In A Matrix. Please Help!

Sep 7, 2007

Hello Guys,
I am working on a matrix report which has several row groups and 1 column group. After execution, the column group wil end up with several columns containg numeric counts. I would like to have the grand total for each "column group" column as a last row on this report.
For row groups you can just right click "Subtotal", but that is not possible for column group. Could someone please help me to find a clever way of accomplishing this, please. Thank you so much for your help!

View 7 Replies View Related

Role Defined Wrong Grand Total Result

May 22, 2008

HI,

In my cube I have defined a role where thet user can only browse certain dimention value. BUt in grand Total the result showing is for all the diemnsion values.

for example The user restricted to browse only Australia and UK Country , But in grand total its showing the SUM of all the country.

Any help will be appreciated.

Thanks

View 3 Replies View Related

Transact SQL :: Grand Total At Button Of Result Set Of Query

Jul 16, 2015

I have a script that produce a result set that is almost complete. I have a new requirement come up to put a Total at the button of the result set of my Query.

I'm using SQL Server 2008
;WITH CTE AS
(
SELECT
Case WHen left(MONTHDate,3)='Jan' Then '01'
WHen left(MONTHDate,3)='Feb' Then '02'
WHen left(MONTHDate,3)='Mar' Then '03'
WHen left(MONTHDate,3)='Apr' Then '04'
WHen left(MONTHDate,3)='May' Then '05'

[code]....

View 4 Replies View Related

Power Pivot :: Sum Of The Parts Not Equaling Grand Total?

Nov 18, 2015

I've created a measure counting instances of unique identifiers utilizing the following formula. However, the Grand Total does not equal the sum of the sub-totals.

=CALCULATE(COUNTROWS(DISTINCT('Rawdata File'[Identifier])),ALL('Rawdata File'[Identifier]))

View 8 Replies View Related