Query By Year Group By Total Students Sort By Total For Each County

Jul 20, 2005

I haven't a clue how to accomplish this.
All the data is in one table. The data is stored by registration date
and includes county and number of students brokne out by grade.

Any help appreciated!

Rob

View 4 Replies


ADVERTISEMENT

Sort On Group Total: How?

May 18, 2006

How do I sort on a group total item?

View 6 Replies View Related

Reporting Services :: SSRS Group Total Expression - Add Total Disabled

Oct 26, 2015

For some reason my Add Total is grey out, when i tried to add grand total using some expression.

I have two row & two column groups?

Is there any alternative or how can i enable add total? using expression..as you can see in my Attached Image

I'm using iff condition in my expression.. 

View 15 Replies View Related

Query For Total Sum By Monthly / Year Wise By Reference Code

Feb 20, 2015

In the Reference_Master table I have Total count based on date for each RefCode. Can I get the total count of each RefCode by monthly wise for each year

Reference_Master

RefCode Date Count
100012/18/20074
100012/19/20078
100012/20/20074
100022/18/20073
100022/19/20072
100022/20/20076

[Code] ...

I need the result as below

Result

RefCodeMonth/YearTotalCount
10001FEB-200716
10002FEB-200711
10003Mar-20077
10004Mar-20073
10005Mar-20072
10001FEB-200816
10002FEB-200811
10003Mar-20087
10004Mar-20083
10005Mar-20082

View 8 Replies View Related

How To Add The Total Balances Of Previous Year Into The Next Year?

Apr 13, 2006

I'm working with a table called SALFLDGAMS where it has two columns that I need to work with. One of the columns is Period which has years from 2000 to 2005 and the other column is called amount which has the balance for that year. Let me explain in a little more detail. There are account codes associated with the years as well so there will be many places where for example the year 2000 will show up with a given value. What I'm trying to do is to create a view which has a BeginBalance column which adds lets say all the values for 2000 and sticks them to 2001. So what I'm saying is all the values from the previous year I want them in the current year. All in one column. Thanks for the help guys.

View 8 Replies View Related

Total Page Writes/total Amount Of Data Change In A Set Period Of Time

Jun 9, 2004

Does anyone know how I can determine the number of page writes that have been performed during a set period of time? I need to figure out the data churn in that time period.

TIA

View 5 Replies View Related

Aggregated Subquery - Sum Total Trips And Total Values As Separate Columns By Day

Feb 26, 2014

Very new to SQL and trying to get this query to run. I need to sum the total trips and total values as separate columns by day to insert them into another table.....

My code is as follows;

Insert Into [dbo].[CombinedTripTotalsDaily]
(
Year,
Month,
Week,
DayNo,
Day,
Trip_Date,

[Code] .....

View 3 Replies View Related

SQL Server 2008 :: Pulling Daily Total From Cumulative Total

Jun 28, 2015

I have a table that writes daily sales each night but it adds the day's sales to the cumulative total for the month. I need to pull the difference of todays cumulative total less yesterdays. So when my total for today is 30,000 and yesterday's is 28,800, my sales for today would be 1,200. I want to write this to a new field but I just can't seen to get the net sales for the day. Here is some sample data. For daily sales for 6-24 I want to see 2,000, for 6-25 3,000, 6-26 3,500, and 6-27 3,500. I'm thinking a case when but can't seem to get it right.

CREATE TABLE sales
(date_created date,
sales decimal (19,2))
INSERT INTO sales (date_created, sales)
VALUES ('6-23-15', '20000.00'),
('6-24-15', '22000.00'),
('6-25-15', '25000.00'),
('6-26-15', '28500.00'),
('6-27-15', '32000.00')

View 9 Replies View Related

Transact SQL :: Total Number Of Days Between Two Dates By Year?

May 6, 2015

I am currently working on a T-Sql query(Sql server 2008) to calculate total no of days between date ranges by year

Table:

Start Date End Date
01/01/2013 04/30/2014
11/01/2014 05/31/2015
06/01/2015 12/31/2015

My expected result.

2013 - 365
2014 - 181
2015 - 365

Note:

Date range can span b/w  multiple years

Date ranges will not overlap

I just want the total number of days covered by the range for each year.

Is there any simple way to do this calculation.

View 9 Replies View Related

What's Microsoft Doing About Providing Page Numbering Per Group And Total Pages Per Group?

Nov 27, 2007

Hi!

I've posted a feedback with Microsoft to see if we can get them to fix the issue described below, but so far no one from Microsoft has commented to let us know what they're doing about this problem! I'm posting this here to see if maybe we can get more people to rate this feedback or chime in on what a pain it is! Please feel free to add your own comments or how you had to work around this issue and whether or not you think this is something Microsoft should be addressing NOW.


https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=311679

Provide Individual Page Numbering per Group and Total Pages per Group

Currently in a Reporting Services report, you can't readily reset the page number for each group in a table, nor can you display the total number of pages per group. For example, if I'm printing invoices and each invoice is a separate group, I'd like to be able to print "Page 1 of 5" , "Page 2 of 5" etc. for the first invoice, then "Page 1 of 3" when the next invoice begins, and so on. This was easy in Crystal Reports. I realize that Crystal Reports has a two-pass process that enables that kind of pagination. However, this is REALLY important functionality that's just missing from Reporting Services and I'm hoping you'll provide it REALLY SOON! Yeah, I know there are work-arounds if you can know exactly how many rows of information there are on each page. But gosh! That's not practical, especially if you have second level groups inside the main group or text blocks in rows that can 'grow' to more than one line. I've read a couple of work-arounds, but none of them works correctly and consistently when more than one user is running the same report or when you print the report while you're looking at it on the screen. I still may need access to the overall report page number and the overall total number of pages, so don't get rid of that. It's just that if you're doing this already for the entire report, I don't see why you can't do it per group! Lots of people have been asking for this for years, and I don't understand why it hasn't been implemented.


I've read a few articles on this topic, but no one has come up with a decent work around. My theory is that Microsoft should be addressing this immediately. This is major functionality that's just plain missing from SSRS and should have been there from the start. If anyone from Microsoft can let us know what's going on with this issue or if anyone would like for me to clarify this further, feel free to let me know.


Thanks!
Karen

View 1 Replies View Related

Total Record Count - Pagination With Total Rows

Jul 26, 2013

My table contains 1000 records,

I need to know the total record count with the below paging query

SELECT EmpName,Place
FROM EmplyeeDetails ORDER BY Place
OFFSET 10 ROWS FETCH NEXT 10 ROWS ONLY;

How to get?

View 2 Replies View Related

Adding Subreport Total To Main Report Total

Apr 28, 2006

Hi, can anyone help?

I have created a Report using Visual studio-the report displays a subreport within it.

On the Subjective Report I have 12 values for each month of the year.

For the first month the value is =sum(Fields! Month_1.Value), and I
have named this text box SubRepM1
The name of the subreport is subreport1'.

On my Main Report, again I have 12 values for each month of the year.
For the first month the value is =sum(Fields! Month_1.Value)*-1, and I
have named this text box 'MainRepM1'
The name of the main report is 'GMSHA Budget Adjustment Differentials'

The report displays both of the subreport and main report values
but I now need to total these values together for each month in order to
produce a grand total.

I have tried using the following to add the totals for Month 1 together,
=subreport1.Report.SubRepM1 + MainRepM1
but this does not work and I get the following error message The value expression for the text box 'textbox18'contains an error [BC30451] Name subreport1 is not declared'.

I feel that it should be a simple matter of adding the two sets of values together but Im having major problems trying to get these totals to work.

Can anyone help, thanks

View 7 Replies View Related

Reporting Services :: How To Get Running Total Depending On ID / Year And Month

May 28, 2015

I want to get a running total. But its a bit tricky. I have data depending on ID, YEAR, Month, Value like the following.

ID, Year, Month, Value
36002084 2014 Jan 8391
36002084 2014 Jan 0
36002084 2014 Jan 3440
36002084 2014 Jan 0
36002084 2014 Jan 548
36002084 2014 Jan 109
36002084 2014 Jan 125

I wrote the Query below

select
[UEP ID],[Year],[Month],sum([Total VtM EUR]) 'Running Total'
from
RegData
where
[UEP ID]=36002084 and [Year]=2014
group by [UEP ID],[Year],[Month]

I got this output

36002084 2014 Apr 887620
36002084 2014 Aug 789440
36002084 2014 Dec 528453
36002084 2014 Feb 606627
36002084 2014 Jan 331613
36002084 2014 Jul 681314
36002084 2014 Jun 330384
36002084 2014 Mar 1044301
36002084 2014 May 671818
36002084 2014 Nov 465576
36002084 2014 Oct 590147
36002084 2014 Sep 527861

Now I need to get a output like this,

36002084 2014 Jan 331613
36002084 2014 Feb 938240

A running total depending on the ID, Year and Month. Although I showed this for two steps I need to get it from one query.

Question is - To get a running total based on ID, Year, Month. If I do the normal group by and sum query I could get it for all the months for each id. But need to then add January value to February and updated February value.

View 10 Replies View Related

ROLLUP - Retrieve A List Showing Total Repair Cost Per Year

Mar 1, 2014

I have a question regarding ROLLUP and how to use the operator.

I've been asked to retrieve a list showing the total repair cost per year displaying Transport Type, Manufacturer and Transport. The query I used is:

SELECT ty.Description AS TransportDescription,
t.SerialNumber AS Transport,
ty.Manufacturer,
SUM (r.RepairCost) AS RepairCost,
YEAR(r.EndWorkDate) AS [Year]

[Code] .....

How could I use the rollup function to display subtotals per Transport Type, Manufacturer and Transport?

View 1 Replies View Related

Group By Sum And Total

Oct 22, 2007

we have following table sturcture

CREATE TABLE [PPE_STOCK] (
[REC_ID] [int] IDENTITY (1, 1) NOT NULL ,
[PPE_ID] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[PPE_NAME] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[DATE_RECEIVED] [datetime] NULL ,
[QUANTITY_PPE] [int] NULL ,
[LOGIN_ID] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO



CREATE TABLE [PPE_ITEM_ISSUE] (
[PII_ID] [int] IDENTITY (1, 1) NOT NULL ,
[EMP_ID] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[QUANTITY_ISSUE] [int] NULL ,
[REMARKS] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[LOGIN_ID] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO





CREATE TABLE [EMPLOYEE] (
[REC_ID] [int] IDENTITY (1, 1) NOT NULL ,
[EMP_ID] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[FIRST_NAME] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[LAST_NAME] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[NAME] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[LOGIN_ID] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO


data insert like this

insert into ppe_stock (PPE_ID,PPE_NAME,QUANTITY_PPE) values
('SH01','SAFETY HELMET',100)
insert into ppe_stock (PPE_ID,PPE_NAME,QUANTITY_PPE) values
('SH02','SAFETY GLOVES',100)
insert into ppe_stock (PPE_ID,PPE_NAME,QUANTITY_PPE) values
('SH03','SAFETY SHOES',100)
insert into ppe_stock (PPE_ID,PPE_NAME,QUANTITY_PPE) values
('SH04','SAFETY GLASSES',100)
.....



insert into ppe_item_issue (EMP_ID,PPE_ID,QUANTITY_ISSUE) values
('p0006367','sh01',2)
insert into ppe_item_issue (EMP_ID,PPE_ID,QUANTITY_ISSUE) values
('p0006365','sh01',1)
insert into ppe_item_issue (EMP_ID,PPE_ID,QUANTITY_ISSUE) values
('p0006366','sh02',5)
insert into ppe_item_issue (EMP_ID,PPE_ID,QUANTITY_ISSUE) values
('p0006367','sh01',5)
.....



insert into EMPLOYEE(EMP_ID,NAME,POSITION_code) values
('p006367','Martin','1')
insert into EMPLOYEE(EMP_ID,NAME,POSITION_code) values
('p006365','Peter','2')
insert into EMPLOYEE(EMP_ID,NAME,POSITION_code) values
('p006366','Jones','3')
insert into EMPLOYEE(EMP_ID,NAME,POSITION_code) values
('p006368','Peter','4')
.....


How can retrived the data by the query like this group by sum
and total.

PPE_STOCK : SAFETY HELMET total QUANTITITY_PPE 100
-------------------------------------------------------
EMP_ID, NAME, QUANTITY_ISSUE, SUM......%
-------------------------------------------------------
P0006367..Martin.....1...
p0006365..Peter......2
p0006366..Jones......1
-------------------------------------------------------
..............................4......4%

PPE_STOCK : SAFETY GLOVES total QUANTITITY_PPE 100
-------------------------------------------------------
EMP_ID, NAME, QUANTITY_ISSUE, SUM......%
-------------------------------------------------------
P0006367..Martin.....5...
p0006365..Peter......2
p0006366..Jones......5
-------------------------------------------------------
..............................12......12%


PPE_STOCK : SAFETY SHOES total QUANTITITY_PPE 100
-------------------------------------------------------
EMP_ID, NAME, QUANTITY_ISSUE, SUM......%
-------------------------------------------------------
P0006367..Martin.....4...
p0006365..Peter......4
p0006366..Jones......6
-------------------------------------------------------
..............................14......14%
...
...


Regards
Martin

View 5 Replies View Related

Sum Per Group Rather Than Total

May 21, 2008

Can somebody please help me how to show a total per 'group' in a table, rather than the total sum?

I have a report with a list that's grouped per project. This list has a table showing amounts paid to this project. As a footer to the table, I'd like a sum that shows the total amount paid per project.

Unfortunately if I run the report on more than one project, the list and table will still be grouped correctly, but the 'sum' will be the total sum rather than the sum of just that given project.

Eg:

Project 1
1-7-2007, 20,000
2-7-2007, 40,000
Total, 100,000

Project 2
2-7-2007, 40,000
Total, 100,000

I've tried all I can think of to get the 'group sum' rather than the total sum, and am sure I'm missing something really basic. I hope somebody here can help me.

Thanks in advance,
Maria

View 6 Replies View Related

Group Total Summary Help

Apr 12, 2007

I hope someone can help me with this one. I can't seem to find a way to solve my problem. I am converting a report from Crystal to RS. In Crystal I am using global variables to keep track of group totals for a final summary. I need a similar result from RS. Data example










Group A


PK Field
Summary Data Field

1
250

2
300

Group A Total
550

Group B


3
100

4
50

Group B Total
150

Grand Total
700



The underlying query contains detail data and I am using a table with two group levels. All details are hidden.



To calculate the totals at the detail level I need to know what the total value for the entire group is. This leads me to my problem, it is not possible (as far as I can tell) to summarize a summary (I get an error). I have tried using the code window to store variables but the value returns a 0. I found a suggestion here http://msdn2.microsoft.com/en-us/library/bb395166.aspx under Distinct Sum, but I can't call the function using the Sum command given that the formula to calculate the value is already using the sum command. I hope this makes sense.



Thanks,

Simone

View 9 Replies View Related

Total Of A Group In Table

Sep 20, 2007



I have a report that looks like below. It's grouped by Product then by Year. I want to include within the Product grouping and item called "All products". If I swapped order of Grouping to Year then Product, I could simply add a SUM in the group header, but the users don't want it displayed that way round.
Any suggestions for "All products" ?
Thanks
Richard

Revenue
Product 1
2007
2008
2009
Product 2
2007
2008
2009
etc

I want to add
All products
2007
2008
2009

View 2 Replies View Related

Creating A Sub Total Using Group By

Apr 5, 2008

I have a table that I want to summarize the amount column and have it show the sub totals for the employee for each pay period. For example:

EMPLOYEE PerEndDate EarnCode Amt
A 8/1/2007 Hourly 100
A 8/1/2007 Peices 250
B 8/1/2007 Hourly 75
B 8/1/2007 Pieces 300
A 9/1/2007 Hourly 50
A 9/1/2007 Pieces 200
B 9/1/2007 Hourly 100
B 9/1/2007 Pieces 200

What I want to show is

Employee PerEndDate Amt
A 8/1/2007 350
A 9/1/2007 250
B 8/1/2007 375
B 9/1/2007 300

I'm using:


SELECT PayPeriodNo, EMPLOYEE AS PayEmpNo, MIN(PerEndDate) AS PerEndDate, SUM(AMT) AS Amt
FROM dbo._vPayroll
GROUP BY PayPeriodNo, EMPLOYEE
ORDER BY PayPeriodNo, PayEmpNo

And the quantity is showind grand totals for each instance

Employee PerEndDate Amt
A 8/1/2007 600
A 9/1/2007 600
B 8/1/2007 675
B 9/1/2007 675

Can anyone help me with this?
Thanks
Leo

View 2 Replies View Related

Counting Total Rows When Using GROUP BY

Oct 20, 2007

hi,
i have a stored procedure SELECT UserName AS Visitor, COUNT(VisitID) AS TotalVisit
FROM UserVisits
WHERE (ProductID = @ProductID) AND (AnonimIP IS NULL)
GROUP BY UserName
UNION
SELECT AnonimIP AS Visitor, COUNT(VisitID) AS TotalVisit
FROM UserVisits AS UserVisits_1
WHERE (ProductID = @ProductID) AND (UserName IS NULL)
GROUP BY AnonimIP
this will return something like:
zuperboy90 - 4 visits
ANONIMOUS - 6 visits
85.104.103 - 2 visits etc
how can i count the rows returned in both selections (4+6+2 = 12) ?
thank you

View 9 Replies View Related

SQL Server 2008 :: Group By Total Sum

Oct 31, 2015

Despite the products are different, I want to get the sum of all products according to customer card.(client ref)

select (ORF.PRICE) *( ORF.AMOUNT - ORF.SHIPPEDAMOUNT) from [.dbo.LG_032_01_ORFLINE ORF JOIN [.dbo.LG_32_01_ITEMS ITM ON ORF.STOCKREF = ITM.LOGICALREF where ORF.CLIENTREF = XXX

View 1 Replies View Related

How Can I Sum By Group Total Instead Of By Each Detail Line

May 16, 2008



I hope someone can help with this problem. I'm new to SSRS 2005, having used Crystal Reports for several years

Our General Ledger Accounts consist of Dept Project AccountCode Each section is 4 digits.
22000000XXXX

I have the following sections in the report table:

Detail -- amount per transaction per account

Detail group -- sum of the transactions per account

Dept Project group sum of all the transactions for the Dept Project


DATE AMOUNT Budget
Detail 222100001234 4/5/2008 $100 $20,000
222100001234 4/10/2008 $200 $20,000

Detail group 222100001234 $300 $20,000

Detail 222100005678 4/12/2008 $400 $40,000
222100005678 4/11/2008 $500 $40,000

Detail group 222100005678 $900 $40,000

DeptProjectGroup 22210000 $1200 $60,000


As shown above, I sum the amounts per account in the detail group, and the amounts per DeptProject in the DeptProject Group. This works fine for the amounts.

I also placed the budget amount per account in the detail group section. This works also. But when I try to total the budget amount per DeptProjectGroup, I get the total for each transaction instead. In Crystal I would have it total by group, but I don't know how to do that in Reporting Services.

Any help would be much appreciated.

Thanks,

Sue

View 6 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

Total Spaceused And Total Allocated

Jul 20, 2005

Anyone has a "one sql statement" to get the total spaceused and totalspace allocated of an instance ? ie same as sum of relevance fieldsfrom sp_spaceused for each database in an instance, that works accrossversion of mssql from 6 onward.ThanksKD

View 1 Replies View Related

Reporting Services :: Unexpected Group Row Total

Aug 17, 2015

I have an SSRS report with 2 Row Groups and 2 Column Groups.  A total row appears before the detail when I run the report.  I'm not sure why it appears or how to remove it.Generally, I'll click on a group, add a total row before or after, and then a line is inserted with "Total" labeled.  That's not what is happening here, I see now total row, it just appears when previewed.  

View 3 Replies View Related

Reset Total Page Number In A Group

Sep 26, 2006

I know how to reset the page numbers with each group, but how do you reset the total page number within each group.

EX. Code for page of total pages

="Page " & Globals.PageNumber & " of " & Globals.TotalPages



EX. Code to reset within a group
Custom Code:
Shared offset as Integer
Shared currentgroup as object

Public Function GetGroupPageNumber(group as Object, pagenumber as Integer) as Object
If not (group = currentgroup)
offset = pagenumber - 1
currentgroup= group
end if
return pagenumber - offset
end function

=Code.GetGroupPageNumber(ReportItems!Category.Value(grouping),Globals!PageNumber)

What I need is code for a combination of the two...to display code for page of total pages that resets within a group.

Any help is greatly appreciated.
Thanks!

View 4 Replies View Related

Aggregate Total Acreage And Group By For Mail Merge

Dec 1, 2005

I was helped on an earlier question to complete my mail merge with the following code:
selectYourTable.*
fromYourTable
inner join --DistinctNames
(selectMax(PrimaryKey) as PrimaryKey
fromYourTable
group by FirstName,
LastName) DistinctNames
on YourTable.PrimaryKey = DistinctNames.PrimaryKey
Basically this code queries my mailing list and ensures that i do not send mutiple letters to one person at the same address who might be in the batabase more than once. However, the reason they are in there more than once is that they might own additional properties. Anyway, I have a column that includes their acreage for each property in each record and I would like to add those up for each person during my query. Thought anyone? Thanks!

View 3 Replies View Related

SQL Server 2012 :: Select Top X% From Group Based On Total?

Mar 21, 2014

What I need to do it select the top 80 percent of records per group based on the group total. To be clear I am not trying to just grab the top x percent of rows.

Table 1:

DealerID RepairID
1 1
1 2
1 3
1 4
1 5
1 6
1 7
1 8
1 9
1 10
2 11
2 12
2 13

Table 2:

RepairID Tire
1 225/65R17 102T
2 225/65R17 102T
3 225/65R17 102T
4 235/60R18 102V
5 235/60R18 102V
6 235/60R18 102V
7 235/60R18 102V
8 205/55R16 89H
9 205/70R15 89H
13 225/65R17 102T

Table 1 has the total number of repair orders per dealer. This can be obtained by simply grouping on DealerID and counting the number of RepairIDs.

Table 2 has information on some of the repair orders and it is needed to select the top 80% of tire sizes. This table will be joined to Table 1 and grouped by DealerID and Tire.

Desired Output:

DealerIDTire RepairsOfThisTireRepairCount PercentOfTotalRepairOrders
1 235/60R18 102V4 10 40
1 225/65R17 102T3 10 30
1 205/55R16 89H1 10 10
2 225/65R17 102T1 3 33

The equation I am given to calculate the top tires per dealer is as follows: Total # of tires for the size / Total # of repair orders per dealer.

Here is what I have so far though I think I might have to rewrite all of it.

SELECT
DealerID ,
COUNT(RepairID)
INTO #TotalDealerRepairOrders
FROM
dbo.Table1
GROUP BY
DealerID
SELECT

[code].....

View 9 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

SQL Server 2008 :: Get Total Number Of Attachments Of Group ID

Sep 10, 2015

Copy and paste the code to review sample:

Create table #temp(
ID varchar (20),
ID_BegAttach varchar (20),
ID_EndAttach varchar (20),
ID_AttachLvl varchar (20),
ID_NumofAttach varchar (20)

[Code] ...

I'd like to get the column ID_NumofAttach to be populated by the total number of ID_Attachlevel column by the same ID_BegAttach or ID_EndAttach and populate where ID_Attachlevel is 0.

View 9 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

Reporting Services :: Getting Total Of Group Values In SSRS

Jun 26, 2015

I am trying to build a report using SSRS.I need to retrieve a distinct value from my initial query and place this in the header or group row.The detail lines are hidden but may be expanded(shown).I then have another group row in which I require the sum of all distinct values from my previous group row.To explain pictorially I have the following:

-Scroll right to see the pink squares.Note that the total in the pink square is the sum of the individual var3 values distinct so 10 even when var3 =4 in euro currency.
-I have tried using sum(var5) but this will give the incorrect value as it sums all detail lines not just the distinct values.
-I have tried using another dataset but when referenced the grouping behaviour expected is non existent so where I expect 40 I get 80 i.e. sum disregarding values of var1 and var2 although these are the grouping variables.
-I need it to be dynamic so I am unable to use IIF(...).

If I could get var4 or var6 to be the totals I require I could work with that.I have also tried the following custom code:

Dim public unitLimit as Double
Public Function Getcpvalue(ByVal limit_amount1 AS Double) AS Double
unitLimit = unitLimit+ limit_amount1
return limit_amount1
End Function

[code]...

View 2 Replies View Related

Analysis :: Order Total For Orders Containing A Specific Product Group?

May 27, 2015

I have a request for being able to show ordertotals for those orders that contains a specific productgroup.

Case

Order 1   ProductGroup   Value
A 20
B 40
C 40
Total 100

Order 2   ProductGroup   Value
A 20
D 40
Total 60

So if i slice OrderTotal by ProductGroup the result will be

A 160
B 100
C 100
D 60
ALL            160

how to solve this.

View 9 Replies View Related







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