T-SQL (SS2K8) :: GROUP BY CUBE Aggregation - Pivoting On 2 Totals

Aug 1, 2014

I'm trying using the GROUP BY CUBE aggregation. Currently I have this working as such:

ISNULL(CONVERT(VARCHAR,Date), 'Grand Total') Date
,ISNULL([1 Attempt],0) [1 Attempt]
,ISNULL([2 Attempts],0) AS [2 Attempts]
,ISNULL([3 Attempts],0) AS [3 Attempts]
,ISNULL([4 Or More],0) AS [4 Or More]

[Code] .....

Basically this is used to work similar to a Pivot table in excel. My data will look as follows:

Date 1 Attempt2 Attempts3 Attempts4 Or MoreTotal
2012-09-04 239 68 2 8 317

The problem I'm having is the Total column. Although this is summing the line values correctly, the total should be based on the sum not count of attempts i.e. 1 x 239, 2 x 68, 3 x 2, 4 x 8

If I change the FROM select clause to use SUM instead of COUNT

,ISNULL(AttemptsFlag,'Total') as Attempt
,SUM(NoOfTimes) AS Totals

It will return the correct Total amount but not the right numbers for the Attempt groupings...

View 1 Replies


Reporting Services :: Calculating Grand Totals From Group Totals

May 9, 2015

I have some data grouped in a table by a certain criteria, and for each group it is computed a subtotal for the group. Of the values from each of the group, I want to create a grand total on the report by adding every subtotal from each group.

Group1              Value
Sub Total 1:         30

Group2                 Value
Sub Total 2:           40

Now, I would like to be able to add subtotal 1 (30) to subtotal 2 (40) and my grand total would be 70. Can I accomplish this task in SSRS?

View 5 Replies View Related

T-SQL (SS2K8) :: PIVOT Without Aggregation

Jun 28, 2015

I have a table imported from a legacy Oracle database that stores values vertically in name/value pairs. I store it in table-type variable that is an exact copy of the structure:

( DataSourceCHAR(8)

If this information were pivoted horizontally: OMNI_NUMBER would be the primary key.

TIMESTAMP is a 10-digit integer that represents the number of seconds since 1/1/1970 UTC that requires additional conversion. DATA_TYPE is not the data type. It is a general categorization of the next two columns.PARAMTER would be the column headings if it were horizontal..PARAMETER_VALUE would be the data value in that column.

I would like to try to use PIVOT to list the PARAMETER column values as column headers. This seems to work fine. What's confusing me is that I'd like it to list the PARAMETER_VALUE column values as raw data, just as it is in the source version, without having to apply some sort of aggregate function to it. Here's a CSV sample of the data you can paste into Excel. I'm trying to transform this:



But I don't want the sum of the values or the average of the values, just the values. The PIVOT syntax seems to require an aggregate operation.

View 2 Replies View Related

GROUP BY Without Aggregation -- Is It Possible ?

Jan 14, 2004

Here is what I want to do.

I have a database with HotelInfo

hotelphone etc etc

I want to display all hotels I have grouped by state

SELECT hotelname, hotelcity, hotelphone
FROM HotelInfo
GROUP BY hotelstate

So basically I was For eg.

hotel1 Dallas xxx-xxx-xxxx
hotel4 Plano xxx-xxx-xxxx

hotel2 San Fransisco xxx-xxx-xxxx

How can I group by without Aggregation ?

View 5 Replies View Related

T-SQL (SS2K8) :: Daily Sum Aggregation Script

Jul 14, 2015

I have a problem that requires me to write Daily sum aggregation script, I wrote the script; But what I'm not sure of is the "Daily" part, my script will give me only give me results when I execute it. How to make this code daily?

,SUM(Cost) AS Cost

[Code] ......

View 1 Replies View Related

Is There A Way To Group Sub Totals?

Nov 14, 2007

Is there a way to display the sum of a group of a field?
I've created a group, but when I put the expression of SUM(Field) in the group footer, it gives me the total of Field for the whole dataset.
Is there a way I can display the just the Totals of the Groups?

so if my data looks like :

a | 1
a | 2
a | 3
b | 4
b | 5

b | 6

I want to display :

a | 1
a | 2
a | 3
Total a | 6
b | 4
b | 5

b | 6

Total b | 15

but instead, when i add the SUM expression into the footer group, I get :

a | 1
a | 2
a | 3
Total a | 21
b | 4
b | 5

b | 6

Total b | 21

View 8 Replies View Related

Group Totals On Last Page - How?

Mar 27, 2007

I have a report that groups by dept #, job code and earnings code.

9999 Administration

033 Secretary

200 Regular Pay 44.00 1000.00

300 Sick Pay 8.00 25.00

400 Overtime 3.00 75.00

8888 Janoitorial

055 Janitor

200 Regular Pay 24.00 800.00

300 Sick Pay 4.00 15.00

400 Overtime 1.00 45.00

On the last page of my report I want to sum the earnings totals by earnings number. For Example:


200 Regular Pay 68.00 1800.00

300 Sick Pay 12.00 40.00

400 Overtime 4.00 120.00

Can this be done?

View 5 Replies View Related

Group Footer With Totals

Nov 30, 2007

I have a tabular report with grouping on the following fields:

Grouped rows: MDC, DrgDesc, ChronicOther
Detail row: Cases

The desired output follows:

Chronic 50
Other 25
Total cases for DrgDesc1= 75

Chronic 20
Other 33
Total cases for DrgDesc2 = 53


I have everything working up to the Total cases for each DrgDesc. I tried adding a group footer to the Cases row, however this sub-totals for Chronic and Other rather than summing them together. Example is below.

Chronic 50
Other 25
Chronic 20
Other 33


How can I achieve the desired result?

View 3 Replies View Related

Problem With Group Totals And Counts

Sep 21, 2007

I am having trouble in SRS determining the distinct count and total for a very complex report.
Basically my dataset return 234 rows. In my report I am using a list (I have to use a list instead of a table for exporting reasons) and I am grouping by accountID.
If I do a CountDistinct(Fields!accountid.value) I still get 234. It's almost like it's not taking in the filter of the group.
I can do a RunningValue for each value and I see it count from 1-23. So I know that there are only 23 values being diplayed.
I also tried doing a CountDistinct(Fields!accountid.Value,"groupname") and I still get the total dataset.

Finally I do not want to do the counting on the dataset (meaning the in the query) because I want the flexibility to use filters for conditional reporting. (I have multiple scenarios in which I need to view the data).

View 8 Replies View Related

T-SQL (SS2K8) :: Calculating Running Totals For Partitions Of Data

Sep 23, 2015

I have table named #t1 and three columns. CODE, Column1, and Column2.

create table #t1 (

And i have some data:

VALUES ('432', 0,100),
('TOTAL FOR 432',0,100),
('TOTAL FOR 4320001',350,390),
('TOTAL FOR 432002',200,100)
drop table #t1

I want to have 4 column (named BALANCE). Balance must be column that represent running totals between two columns (Column1 - Column2) for each group of data. For each group total must start from zero.after total 432 it starts to count again for total 4320001 and again for total 432002. I'm using MS SQL SERVER 2014.

View 9 Replies View Related

Inscope Evaluates To False On Second Row Group (totals Column)

Sep 11, 2007

Hi, I have a matrix with 2 row groups and 1 column group.

CGroup1 Val1

CGroup1 Val2


- RGroup1 Val1

RGroup2 Val1




RGroup2 Val2




- RGroup1 Val2

RGroup2 Val3




RGroup2 Val4








I want to change the row totals at the RGroup2 level. I have put an expression in the measure cell as:

=iif(InScope("matrix1_RowGroup2"), "In", "Out"). Shouldn't the values in the Totals Column on the far right evaluate to "In"? If not, how can I isolate the totals at the RGroup2 level?

Also, I found that when I put =fields!RGroup2.value in the expression for the cell, the Totals Column on the far right is blank but when I put =fields!RGroup1.value the correct value is properly displayed in the Total Column. Why does =fields!RGroup2.value not work?

View 7 Replies View Related

Reporting Services :: Group Totals From Report Items

May 18, 2015

I have a table with a row group "Sales Area" that lists customers per sales area. There is one column with the sales per customer and another column with the planned sales per customer.A third column "Under Plan" is a simple calculation that compares the two Report Items of the sales to the plan and puts a 1 there if plan is higher. My issue is how to get the total of the group "Sales Area", to display the group total of all customers that are under plan. SSRS doesn't let me use aggregate functions on group totals;Unfortunately I cannot pre-calculate the "Under Plan" figure in the query, since this example is a simplified overview (the customers is a distinct count for example...)

View 5 Replies View Related

Reporting Services :: Adding Group Totals In SSRS

Oct 27, 2015

I have a field on my report that uses the following expression to determine the commission amount for each order line.  It works correctly to get the commission amount for each line, however, I need to get a total of the commission amount for each Salesperson. 

My report is grouped in the following manner:

Salesperson, Type of Sale, Invoice Number, then the detail invoice line items (where the formula below reside).  How can I get the totals for the Salesperson and the Type of Sale?

=IIF(Fields!PartIsSerialized.Value=True, (Sum(Fields!OrderLineSubtotal.Value)/Fields!Quantity.Value),Sum(Fields!OrderLineSubtotal.Value))
Fields!TransactionType.Value Like "*USED*", (Parameters!CommissionRateUsed.Value*.01),

[Code] ....

View 2 Replies View Related

Report Services 2000 Totals On Group Are Incorrect. Summing Duplicate Values

Dec 19, 2007

Hello Everyone
I've created a report with a simple dataset that is similar to this

City , RequestID, Request Amount, ClaimID, ClaimAmount
El Monte 791 52,982.00 2157 41,143.75
El Monte 3691 11,838.00 3140 8,231.14
El Monte 3691 11,838.00 3141 990.00
El Monte 3691 11,838.00 3142 2,615.00

So I group by City, RequestID. On the first group I specified the expression to be City and in the header I list the city and in the footer I list the sum of Request amount. On the second group I specified the group by Request so in the header I placed requestID and on the footer I placed Request Amount. I set request information to hide the duplicates and I even add =Sum(Fields!RequestApprovedGrandTotal.Value,"GroupByRequestID") the scope of the group. But this is what I get:

For requestID = 3691 for Request Amount is 35,514.00 not 11,838.00. All the claim sums are correct and they are located on the detail row.

I've read that a work around is to create multiple dataset but I honestly believe that something as simple as this should work on the reporting server 2000. So I've come to the conclusion that I must be doing something wrong. Can someone give me a hand on this. Thanks.

View 6 Replies View Related

Cube/Rollup Without Group By?

Jul 20, 2005

MS has been nice enough to add the Cube and Rollup operators so I canhave totals with my results.But out of shear meanness they won't let me use them unless I use aGroup By clause in my select. I have a proc with 25 fields, and haveno desire to Group By anything- when a certain field changes, I justwant a row with the total of that field.Is this possible?Thanks,Burt

View 2 Replies View Related

Reporting Services :: Row Group Totals On A Drill Down Report Builder Report

Oct 28, 2015

I have a report builder drill down report. I have row groups with totals.    It looks like the attached.   The problem is when the report is not expanded the Grand Totals column is not accurate... it is displaying the totals of one of the rows when expanded.The expression in the Total Show text box is

= Switch (                                                                      
                                         Sum(IIF(Fields!status.Value = "CO", CDbl(Fields!TX_CO.Value),           Nothing)),
  MID(Fields!protocol_id.Value,1,7) = "GENERAL" and                                                  MID(Fields!program_id.Value,1,6)
= "INTAKE",                                                        Sum(IIF(Fields!status.Value = "CO",


Is there any way to not display the expression in the Total columns unless the report is expanded?

View 2 Replies View Related

Trying To Get Daily Totals From Cumulative Totals In A Pivot

Oct 2, 2006

I have been providing sales data for a few months now from a table that is set up like this:

Date WorkDay GasSales EquipmentSales

9/1/2006 1 100.00 200.00

9/4/2006 2 50.00 45.00


As can be seen, the data is daily, i.e., on the first workday of September we sold one hundred dollars in gas and two hundred dollars in equipment. On the second workday of September we sold fifty dollars in gas and forty-five dollars in equipment.

Now, however, the data I have to pull from is cumulative. So, using the last table as an example it would look like this:


9/1/2006 1 100.00 200.00

9/4/2006 2 150.00 245.00


To make things more complicated, the powers that be wanted this data presented in this fashion:

Total Sales:


300.00 95.00 etc.

 So, I have been doing a pivot on a CRT to get the data to look like I want. The code is like this:

with SalesCTE (Month, WorkDay, [Total Sales])




datename(month, cag.date),

cag.WorkDay AS [Work Day],

sum(cag.sales_gas + cag.sales_hgs) AS [Total Sales]


Branch ON CAG.[Oracle Branch] = Branch.OracleBranch

group by cag.date, cag.WorkDay


select * from SalesCTE



sum([Total Sales])

for WorkDay

in ([1],[2],[3],[4],[5],,[7],,[9],[10],[11],[12],[13],[14],[15],[16],[17],[18],[19],[20],[21],[22],[23])

) as p

So, my question is:

How do I get the data to give back daily totals instead of the cumulative amounts for each workday? If the query was a simple one, I'd do something like

select [1] as [Day 1], [2]-[1] as [Day 2], [3]-[2] as [Day 3], etc.

but the query is far from normal, with the CRT and the pivot. I can't seem to get it to work how I'd like.

Any advice/answers? Thanks in advance!!!


P.S. I don't know how to get it to quit with the freakin' smileys.... I suppose you can figure out what my code is really supposed to look like above. Needless to say, it doesn't include a devil face and a damn music note...

View 12 Replies View Related

T-SQL (SS2K8) :: Group By Performance FK / PK

Jul 10, 2014

We have a medium sized database with the next tables:

- PA: 525000 records
- PR: 780000 records
- R: 1000 records
- B: 45 records





When I group by B.BOEKCODE the query lasts: 10 (or more when 'where' option is added) seconds
When I group by R.BOEKCODE the query lasts less than 2 seconds.

ORDER BY BOEKOMSCHRIJVING Why is the option of B.BOEKCODE a lot slower compared to R.BOEKCODE?

View 9 Replies View Related

T-SQL (SS2K8) :: Group By With Nulls?

Jun 12, 2015

Table Clients is master table with all records, joining against Assets table that may or may not have a matching entry.

Trying to sum an asset type against table and no matter what kind of join I do I cannot get SQL to return a NULL match against the Clients ID value. All I get back are matching rows.

Here's the SQL:

select c.cindex,SUM(a.value) AS 'Total Assets' from Clients c
"the join" Assets2012 a on a.clientid=c.cindex
where (c.ClientClass<=7 AND c.ClientClass<>6) AND a.assettype = 2
group by c.cindex

But no matter what type of join I do, left, right, left outer, inner, I am not getting back NULL values for client records that have no matching asset records.

Strange thing is, by removing the "and assettype=2" part I get the whole database back, with NULL's but not the range I'm looking for.

View 2 Replies View Related

T-SQL (SS2K8) :: Select MAX Per Group?

Jul 22, 2015

I want to select all the Rows with Grouping done by PARENTID and Max value in REVNR per GROUP.

I am able to get one MAX row per Group but not all the Rows which has MAX value.

Here is my Table sample.



In the result how do i get just 2 ParentIDs but multiple rows of DocumentID.

View 7 Replies View Related

T-SQL (SS2K8) :: Group Columns To One Row In A Table

Apr 1, 2014

I have a table with the following columns

74 ,1,2,beck
74 ,1,2,greg
74 ,1,9,mike
74 ,1,9,laggo
74 ,2,2,beck
74 ,2,2,greg
74 ,2,9,mike
74 ,2,9,laggo

I am trying to get the result as follows.

Num Id Final Value

74 1 2,beck,greg;9,mike,laggo
74 2 2,beck,greg;9,mike,laggo

I tried to use Stuff and XMLpath but it is not giving me distinct results.

View 4 Replies View Related

T-SQL (SS2K8) :: MIN Across Multiple Columns With GROUP BY

Jan 13, 2015

How to get the lowest U.Price along with FX and Rate.


1280 19.1196 EUR 3.85
1280 46.2462 USD 3.63
1280 6.32 RM 1.00

Required output.


1280 6.32 RM 1.00

View 4 Replies View Related

T-SQL (SS2K8) :: Query To Group By Only One Column

Jan 21, 2015

How can we write the query using groupby. I need to have group by only one column. Is it possible through subquery?

select col1, col2, col3
from testtable
group by col1.

View 4 Replies View Related

T-SQL (SS2K8) :: Skipping Group With FOR XML Path

May 18, 2015

I have a table with a text field and a grouping number.

1, A
1, B
1, C
1, D
1, E

2, A
2, C
2, D
2, E

3, A
3, B
3, C
3, F

4, A
4, B
4, D
4, F
4, G

I assemble the text fields into strings by group, using the FOR XML PATH('') function, so that I end up with

1, A-B-C-D-E
2, A-C-D-E
3, A-B-C-F
4, A-B-D-F-G

This all works fine. I now have a requirement to leave out some groups, based on whether the GROUP does or does not contain a certain text. For instance, if I want only GROUPS with 'E', my result set should be

1, A-B-C-D-E
2, A-C-D-E

If I want only those GROUPS that DO NOT have 'E', my result set should be

3, A-B-C-F
4, A-B-D-F-G

I can (and have) put a condition on the assembled string, but it seems to me that it should be possible to make the cut earlier. That is, rather than assembling the string, scanning it with a Like '%E%' condition and discarding what doesn't meet my needs, I would like to stop scanning the incoming GROUP as soon as a mismatch is detected.

If I want all groups that will not contain an 'A', for instance, it is pointless for the query engine to read in 'A', 'B', 'C', 'D', 'E', put them all together into a string, compare it with a wildcard and throw it away. As soon as it sees the 'A', it has enough information to completely skip over the rest of that group. The abbreviated examples here are trivial, but the real code is not.

All I've been able to dream up so far results in skipping the RECORD that contains the 'A', but not the entire group.

View 9 Replies View Related

T-SQL (SS2K8) :: Display Record Using Group By?

Aug 3, 2015

I would like to display all the products with maximum SeqNo from the table below:




View 3 Replies View Related

T-SQL (SS2K8) :: Update Records Separated By Group

Jun 25, 2014

I have a pretty complex query that returns three records. For simplicity sake, the results can be simulated with this query:

Select 5 AS InternalAuditTeamEmployeeID, 1 as InternalAuditTeamID
Select 11, 2
Select 14, 3;

I want to take this result and update the Flag field to true in my table tblInternalAuditTeamEmployee (CREATE statement below) for any InternalAuditTeamEmployeeID that is less than or equal to the ones in the results above, but by group. My results would look something like this using the data below and the results above.

1 1 619 1

I was thinking I could somehow use ROW_NUMBER(PARTITION BY InternalAuditTeamID ORDER BY InternalAuditTeamEmployeeID DESC), but not sure how to get the results of "WHERE <= InternalAuditTeamEmployeeID For each particular group".


CREATE TABLE [tblInternalAuditTeamEmployee](
[InternalAuditTeamEmployeeID] [int] IDENTITY(1,1) NOT NULL,
[InternalAuditTeamID] [int] NOT NULL,
[EmployeeID] [int] NOT NULL,

[Code] ......

View 3 Replies View Related

T-SQL (SS2K8) :: Group By And Order By - Location Starting With A And B

Jul 7, 2014

I am having below schema.

CREATE TABLE #Turnover (
location varchar(50),
Total int

insert into #Turnover (location,Total) values('A', 500)
insert into #Turnover (location,Total) values('AB', 200)
insert into #Turnover (location,Total) values('ABC', 100)
insert into #Turnover (location,Total) values('BA', 100)
insert into #Turnover (location,Total) values('BAC', 500)
insert into #Turnover (location,Total) values('BAM', 100)

Now i want output order by total but same time i want to create two groups. i.e. location starting with A and order by total and after locations starting with B and order by total.

View 5 Replies View Related

T-SQL (SS2K8) :: Count Record Based On Group

Dec 10, 2014

This my table named myData

CREATE TABLE [dbo].[myData](
[idx] [int] NULL,
[paymentMethod] [nvarchar](200) NULL,
[daerahKutipan] [int] NULL,
[payer] [int] NULL,


I want to calculate the number of payer Group By paymentMethod. The number of payer must be divided by daerahKutipan. So far, my query as follow

select paymentMethod,
COUNT(CASE WHEN daerahKutipan = 1 THEN payer ELSE 0 END) figure_Seremban,
COUNT(CASE WHEN daerahKutipan = 3 THEN payer ELSE 0 END) figure_KualaPilah,
COUNT(CASE WHEN daerahKutipan = 4 THEN payer ELSE 0 END) figure_PortDickson,
COUNT(CASE WHEN daerahKutipan = 5 THEN payer ELSE 0 END) figure_Jelebu,
COUNT(CASE WHEN daerahKutipan = 6 THEN payer ELSE 0 END) figure_Tampin,
COUNT(CASE WHEN daerahKutipan = 7 THEN payer ELSE 0 END) figure_Rembau,


View 1 Replies View Related

T-SQL (SS2K8) :: Group Sum LEN On VARCHAR Column And Concatenate

Jul 3, 2015

I've got a fairly standard query that does a group by a type column, and then sums the lengths of a VARCHAR column. I'd like to add into that a concatenated version of the string always concatenating in primary key order. Is that possible?

View 1 Replies View Related

T-SQL (SS2K8) :: How To GROUP BY With Shortest Distance By Account Number

Mar 11, 2014

Given the following example;

declare @CustIfno table (AccountNumber int, StoreID int, Distance decimal(14,10))
insert into @CustIfno values ('1','44','2.145223'),('1','45','4.567834'),
('2','46','11.5436543'), ('3','44','9.145223'),('3','45','8.567834'),

How can I show the shortest Distance by AccountID and StoreID. Results would look like this;

AccountNumberStoreID Distance
1 44 2.1452230000
2 45 1.5465432300
3 45 8.5678340000
4 45 1.5465432300

View 7 Replies View Related

T-SQL (SS2K8) :: Checking User Active Directory Group

Jul 17, 2014

Is it possible to check for Active Directory group.. ie see if the user running the Stored Proc, is in a specific Active Directory Group? Or if I set up Login's using Active Directory, can I get the Login that way... or will it give me the user's account?

View 6 Replies View Related

T-SQL (SS2K8) :: How To Group Total Count Of Similar Items

Mar 30, 2015

We sell & ship packages that contain multiple items within them. The actual package (we call it the "parent item") is in the same table as the items within it ("child items"). If the record is a child item within a package, its "ParentId" field will contain the ItemId of the package.

So some sample records of a complete package would look like this:

ItemId | ParentId | Name | QtyAvailable
1 | NULL | Package A | 10
2 | 1 | Item 1 | 2
3 | 1 | Item 2 | 3

ItemId's 2 & 3 are items contained within the ItemId 1 package.

Now however, the client wants us to build a report showing all packages (all items where ParentId is NULL) however, they want to see the QtyAvailable of not only the package but the items as well (a total of 15 when using the example above), all grouped into a single line. So a sample report line would look like this:

Name | Available Qty
Package A | 15
Package B | 100

How can I do a SELECT statement that SUMS the "QtyAvailable" of both the parent & child items and displays them along with the package name?

View 6 Replies View Related

T-SQL (SS2K8) :: Outer Join Add Non Matching Rows To Each Order Group?

Mar 30, 2015

In Outer join, I would like to add the outer columns that don't exist in the right table for each order number. So currently the columns that don't exist in the right table only appear once for the entire set. How can I go about adding PCity, PState to each order group, so that PCity and PState would be added as null rows to each group of orders?

if OBJECT_ID('tempdb..#left_table') is not null
drop table #left_table;
if OBJECT_ID('tempdb..#right_table') is not null
drop table #right_table;
create table #left_table


View 2 Replies View Related

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