Aggregation That Results Two Columns

Mar 12, 2007

Hi,
I have following data coming from prior transformations.

Id Unit Name NewFlag
___ __________ ____________

1 JacuzziBox 1
2 Hummer H2 0
3 Waste dumper 0


and so on.

I want aggregate task to result 2 fields (without any GROUP BY-ing), so that one contains total number of rows or records, and one contain total number of records with NewFlag=1.

Just wondering if it is possible by using Aggregate and how.

Thanks for your help in advance,

View 1 Replies


ADVERTISEMENT

Results Doubling Up In Two Columns?

Aug 12, 2014

Why I am seeing double ups in the 'Bin Label' and 'Bin Qty' columns.

My query looks like the following -

Code:
go
declare @whCode varchar(5), @binLoc varchar(2)
set @whCode = '04'
if @whCode = ''
set @whCode = '%'
if @whCode = '02'
set @binLoc = 'S%'

[code]....

why my results are doubling up in the two indicated columns.

View 1 Replies View Related

Combining Results Into Columns

Oct 18, 2013

I have a query (SELECT * FROM Pricing) that produces the following results

Stockcode|ProductID|OurSellPrice|SupplierTypeID|CompetitorPrice
SC0001___|123______|22.45_______|1_____________|25.23
SC0001___|123______|21.45_______|2_____________|25.23

I want to convert this to the following :

Stockcode|ProductID|OurSellPriceType1|OurSellPriceType2|CompetitorPrice
SC001____|123______|22.45____________|21.45____________|25.23

We only have two types of suppliers but not every product is available from each type of supplier so we might get the following results:

Stockcode|ProductID|OurSellPrice|SupplierTypeID|CompetitorPrice
SC0002___|124______|22.45_______|1_____________|25.23

Stockcode|ProductID|OurSellPriceType1|OurSellPriceType2|CompetitorPrice
SC001____|123______|22.45____________|NULL_____________|25.23

View 1 Replies View Related

Results In Parallel Columns

Jun 14, 2006

Hi ,I need to place the results of two different queries in the same resulttable parallel to each other.So if the result of the first query is1 122 343 45and the second query is1 342 443 98the results should be displayed as1 12 342 34 443 45 98If a union is done for both the queries , we get the results in rows.How can the above be done.Thanks in advance,vivekian

View 7 Replies View Related

Search And Results Using Multiple Columns

Feb 10, 2004

I have a DBTable named Vendors which includes the following columns: CompanyName (Name Here)
SBI (Yes, No)
MBE (Yes, No)
SBI (Yes, No)
WBE (Yes, No)

I'm trying to do a search on Vendors where either SBI or MBE or SBI or WBE is equal to 'yes' and then order by company name.

I'm lost on both the search and the results code.

I'd like to have a drop down for the search like this:

<form name="Search" action="vendor_results.asp" method="post">
<Input type=hidden name="validate" value=1>

Show all Vendors who are: <Select name="?????">
<OPTION value="YES">SBI
<OPTION value="YES">SBE
<OPTION value="Yes">MBE
<OPTION value="Yes">WBE
</select>
<Input type="submit" Value="Go">
</form>


Any ideas on how to make this work would be greatly appreciated.

View 3 Replies View Related

Duplicate Results In 2 Columns But Reversed

Feb 17, 2004

I have to write a query which extracts everyone from a table who has the same surname and forenames as someone else but different id's.

The query should have a surname column, a forenames column, and two id columns (from the person column of the table).

I need to avoid duplicates i.e. the first table id should only be returned in the first id column and not in the second - which is what i am getting at the mo.

This is what i have done

select first.surname, first.forenames, first.person, second.person
from shared.people first, shared.people second
where first.surname= second.surname
and first.forenames = second.forenames
and not first.person = second.person
order by first.surname, first.forenames

and i get results like this

Porter Sarah Victoria 9518823 9869770
Porter Sarah Victoria 9869770 9518823 - i.e. duplicates

cheers

View 5 Replies View Related

T-SQL (SS2K8) :: Results Doubling Up In Two Columns?

Aug 12, 2014

I have written some SQL that relates to the (partial) result set below -

I am having an incredibly difficult time trying to work out why I am seeing double ups in the 'Bin Label' and 'Bin Qty' columns.

My query looks like the following -

go

declare @whCode varchar(5), @binLoc varchar(2)
set @whCode = '04'
if @whCode = ''
set @whCode = '%'

[Code].....

View 1 Replies View Related

Adding Columns To A Query From Results

Apr 14, 2014

I have a table called approval_levels in which I have 2 columns:

|LEVEL_ID |NAME |
|1 |app_level_1 |
|2 |app_level_2 |
|3 |app_level_3 |

I have a second table called requests, in which I have 3 columns:

|REQUEST_ID |PRODUCT_NAME |MANUFACTURER |
|1 |wd-40 |Acme |
|2 |Windex |Acme |
|3 |Propane |Acme |

I have a third table which links the two called request_approvals which has 2 columns: REQUEST_ID, LEVEL_ID. When a request is approved for a specific level, I insert a value into this table. So, for example, lets say request 1 has been approved for all 3 levels, request 2 has been approved for only level 1, and request 3 has been approved for level 3, the table would show something like this.

|REQUEST_ID |LEVEL_ID |
|1 |1 |
|1 |2 |
|1 |3 |
|2 |1 |
|3 |3 |

Ok, so here the challenge: I need to show in a report all the requests, create a columns for each level and show whether or not that request is approved for that level. The end result has to be something like this:

|REQUEST_ID |PRODUCT_NAME |MANUFACTURER |app_level_1 |app_level_2 |app_level_3 |
|1 |wd-40 |Acme |X |X |X |
|2 |Windex |Acme |X | | |
|3 |Propane |Acme | | |X |

Keep in mind that if another value is added to the approval_levels table (ie app_level_4), I need to add another column to the table call app_level_4 dynamically.Now, how in the world do I do something like this?

View 1 Replies View Related

Need To Comparte 2 Columns For Non Matching Results

Mar 12, 2008



I have 2 columns from 2 separate tables.

Col1 is AccountName from tblOpportunities
Col2 is BillToName from AccountCodes

I am trying to get the results from tblOpportunities.AccountName that DO NOT also exist in AccountCodes.BillToName



Thanks

View 3 Replies View Related

Incorrect Results From FTS On Multiple Columns

Apr 24, 2006

I'm writing a FTS query which needs to search on two different columns.

E.g. Table contains "Location" and "LocationDescription" columns.
Both columns are FT indexed.
The query also uses AND/OR operators to filter out the results.

I found the following article which gives the solution to the same problem.
Link: http://support.microsoft.com/default.aspx?scid=kb;en-us;286787
Is this problem associated with SQL Server 2005 also?

Making a third column which hold data from first two column is the
only solution or is there any other way to acheive better results?

Shailesh Patel...

View 3 Replies View Related

SQL 2012 :: Results To Show Up As Separate Columns

Aug 22, 2014

We have the below query that pulls benefit ids for employees but it will show each benefit on a separate row but we would like to have just one rows for the employee and columns for each of the benefits.

SELECT
hcd.PersonId,
hcd.PlanYear,
hcd.TaxIdNumber,
hcd.LastName,
hcd.FirstName,
hcd.BirthDate,

[code]....

View 3 Replies View Related

SQL 2012 :: Put Results Into Separate Columns Based On Value?

Feb 25, 2015

I am trying to take the results of a query and re-orient them into separate columns.

select distinct
W_SUMMARYDETAILS.FACILITY_ID,
W_SUMMARYDETAILS.REPORTING_YEAR, (2011 - 2014, I want these years broken out into columns for each year)
W_SUMMARYDETAILS.FACILITY_NAME,
W_DEF_SUMMARYDETAILS.REPORTING_PERIOD (2011 - 2013, I want these years broken out into columns for each year)
From W_SUMMARYDETAILS
full outer join W_DEF_SUMMARYDETAILS
on W_SUMMARYDETAILS.FACILITY_ID=W_DEF_SUMMARYDETAILS.FACILITY_ID and
W_SUMMARYDETAILS.REPORTING_YEAR=W_DEF_SUMMARYDETAILS.REPORTING_PERIOD

As of now the query puts all the years into a single column -- one for DEF_SUMMARY and another for SUMMARY.

I am looking to create 7 additional columns for all the individual years in the results instead of just two columns.

View 9 Replies View Related

Pivot Results With Columns For Movements In Period

Mar 7, 2014

I have a query which returns the movements to and from our warehouse stock, as well as the current stock for each depot and how much is on order. What I need is a kind of pivot so that each item is shown just once, and then summarises the movements in 4 extra columns: Last 30 days, 30-60 days, 60-90 days and 90-120 days. How can I achieve this with my query below? A sample of some of the results is also shown.

select
iv.item,
iv.descr,
ts.loc_total_on_hand [Stock],
ts.loc_code [Depot],
po.qty [On Order],
po.office [Order Depot],

[Code] .....

View 2 Replies View Related

Trouble Putting Sub Report Results In Columns

Apr 30, 2008

Dear all,

I have a sub report that returns a variable amount of results. On occasion many results will be returned and cause one or more extra pages to be created.

The results from my sub report are not very wide so what I would like to do is have the results roll over from the left hand side of the page to the right hand side of the page and then continue this pattern on sunsequest pages until all results are displayed. Very similar to newspaper columns.

Is this possibe? If so how can I go about doing this?

Many thanks in advance,

Trevor Keast.

View 1 Replies View Related

Sort Results By Rank Using FREETEXT On Multiple Columns

Apr 20, 2008

Hi,I am using MS SQL server 2005 and wondering how to sort my results by rank using FREETEXT on multiple columns.  Is there a way to do this? My two colums are:title and description
 
can anyone give any code snippets?

View 1 Replies View Related

Query Names Of Stored Procedure Results Columns?

Mar 2, 2012

I am imagining something you might pass the names of 2 stored procs (an old version and new one), and a query to produce valid parameters. It would then fire off each proc for a set number of executions, while storing off the results in temp tables, and at the end it would do a data compare, and store off performance data from dynamic management views.

Now I know how to get the parameters for a stored procedure out of the catalogue views, but is SQL Server aware at all of the schema of the results of stored procedures that return result sets, becuase I was thinking of doing something like...

INSERT INTO #datacompare(col1,col2)
EXEC mystoredprocedure

... but I can not seem to figure out how to dynamically gather the schema of the result set.

View 1 Replies View Related

SQL Server 2012 :: View Results Not Displaying Nil Columns

Sep 5, 2014

I have a view I've created which displays client sortname, partner and date added which displays 7 results.

When I add another table to this view to display the Industry it then only gives me 4 results as the other 3 results have no Industry instead of giving me the 7 results and showing the Industry column as empty for the other 3.

Is there a way I can make it show all 7 results and havethe column where the industry is empty display the other results instead of not displaying any results at all for them?

Script:
SELECT dbo.cdbClient.cltSortName AS ClientName, dbo.vcltAttrib4.ainTVal AS ClientPartner, dbo.vcltAttrib422.ainDVal AS [Date Added],
dbo.cdbAttribInst.ainTVal AS Inudstry
FROM dbo.cdbClient LEFT OUTER JOIN
dbo.cdbObject ON dbo.cdbClient.cltCategoryID = dbo.cdbObject.objID LEFT OUTER JOIN

[Code] ....

In the above script the cbdAttribInst table has the Industry column I need which is 'ainTVal'...

View 8 Replies View Related

UPDATE A Table With Aggregate Results For Multiple Columns

Jan 8, 2007

Hi everyone. I am updating a table with aggregate results for multiplecolumns. Below is an example of how I approached this. It works finebut is pretty slow. Anyone have an idea how to increase performance.Thanks for any help.UPDATE #MyTableSET HireDate=(Select Min(Case When Code = 'OHDATE' then DateChangedelse null end)From HREHWhere #MyTable.HRCo=HREH.HRCo and#MyTable.HRRef=HREH.HRRef ),TerminationDate=(select Max(Case When Type = 'N' thenDateChanged else null end)From HREHWhere #MyTable.HRCo=HREH.HRCo and#MyTable.HRRef=HREH.HRRef ),ReHireDate=(select MAX(Case When Code = 'HIRE' thenDateChanged else null end)From HREHWhere #MyTable.HRCo=HREH.HRCo and #MyTable.HRRef=HREH.HRRef )

View 2 Replies View Related

Transact SQL :: How To Join Results Of Two Queries By Matching Columns

Aug 10, 2015

I have two queries as below;

SELECT EventID, Role, EventDuty, Qty, StartTime, EndTime, Hours
FROM dbo.tblEventStaffRequired;

and
SELECT EventID, Role, StartTime, EndTime, Hours, COUNT(ID) AS Booked
FROM tblStaffBookings
GROUP BY EventID, Role, StartTime, EndTime, Hours;

How can I join the results of the two by matching the columns EventID, Role, StartTime and EndTime in the two and have the following columns in output EventID, Role, EventDuty, Qty, StartTime, EndTime, Hours and Booked?

View 4 Replies View Related

T-SQL (SS2K8) :: Combine 2 Selects - Insert Results In Two Columns Of New Table

Sep 11, 2014

DECLARE @EmployeeID nvarchar(1000)
DECLARE @FiscalYear nvarchar(1000)

SET @EmployeeID = '101,102,103,104,105'
SET @FiscalYear = '2013,2014'

SELECT Data FROM dbo.Split(@EmployeeID, ',')
SELECT Data FROM dbo.Split(@fiscalyear, ',')
_______________________________________

This is part of a bigger project but I am stuck on this part. I get back 2 result sets

Data Data
101 2013
102 2014
103
104
105

I want to insert the results in a new table 2 columns and get the results below.

New Table
ID Fiscal Year
101 2013
101 2014
102 2013
102 2014
103 2013
103 2014
104 2013
104 2014
105 2013
105 2014

View 2 Replies View Related

SQL Server 2012 :: How To Join Pivot Results With Dynamic Columns

Mar 5, 2015

I have a lookup table, as below. Each triggercode can have several service codes.

TriggerCodeServiceCode
BBRONZH BBRZFET
BBRONZH RDYNIP1
BBRONZP BBRZFET
BCSTICP ULDBND2
BCSTMCP RBNDLOC

I then have a table of accounts, and each account can have one to many service codes. This table also has the rate for each code.

AccountServiceCodeRate
11518801DSRDISC -2
11571901BBRZFET 5
11571901RBNDLOC 0
11571901CDHCTC 0
17412902CDHCTC1 0
14706401ULDBND2 2
14706401RBNDLOC 3

What I would like to end up with is a pivot table of each account, the trigger code and service codes attached to that account, and the rate for each.

I have been able to dynamically get the pivot, but I'm not joining correctly, as its returning every dynamic column, not just the columns of a trigger code. The code below will return the account and trigger code, but also every service code, regardless of which trigger code they belong to, and just show null values.

What I would like to get is just the service codes and the appropriate trigger code for each account.

SELECT @cols = STUFF((SELECT DISTINCT ',' + ServiceCode
FROM TriggerTable
FOR XML PATH(''), TYPE
).value('(./text())[1]', 'VARCHAR(MAX)')
,1,2,'')

[Code] ....

View 1 Replies View Related

Select Query Results In Multiple Columns Based On Type From Another Table

Apr 6, 2008

Using SQL Server 2005 Express:
I'd like to know how to do a SELECT Query using the following tables:

Miles Table:
Date/Car/Miles/MilesTypeID
===============
(some date)/Ford/20/1
(some date)Ford/20/2
(some date)Chevy/30/1
(some date)Toyota/50/3
(some date)Ford/30/3


Miles Type Table
MilesTypeID/MilesType
=================
1/City
2/Highway
3/Off-Road

I'd like the results to be like this:

Date/Car/City Miles/Highway Miles/Off-Road Miles
=====================================
(date)-Ford-20-0-0
(date)-Chevy-0-20-0
(date)-Ford-20-0-0
(date)-Toyota-0-0-50
(date)-Ford-0-0-30

Anyone? Thanks in advance!

View 3 Replies View Related

Getting Results With Stored Procedure From Single Textbox Searching Table With Multi Columns

Feb 12, 2007

I admit I am a novice to queries and stored procedures but understand stored procedures are more secure, so I am choosing to challenge myself with a stored procedure and have not been successful at all.

What I am looking for is a stored procedure which would query the single table named Hospital, which would search from a single textbox the end user would enter the text they are searching for on a Windows VB application but is dependant on a few end user selected items.

1. The end user can select a radio button either "Starts with" or "Contains"
2. The end user can select from 6 checkboxes (Hospitals, Address, City, State, Zip, Type) to limit or expand their search from the columns in the table.

The table has 17columns (CO, PARENTCO, ADDR, POBox, CITY, State, ZIP, TEL, FAX, WEBSITE, HOSP_TYPE, OWNERSHIP, BEDS, CONTACT, TITLE, Email) but I am only allowing the end user to search by the above 6 columns and need to display the above 6 columns plus the phone, fax and website columns.


After the user clicks the search button it displays the results in a listbox and the user will then select the one they want and it displays the information in another set of textboxes.

Hopefully someone can get me pointed in the right direction! Thanks for your help!

View 12 Replies View Related

I4 To I8 In Aggregation

May 30, 2007

I'm using an Aggregation task to summarize an input file by item and week before inserting it into a SQL table.



Two of the fields I'm summing, because their totals per record can occasionally exceed 32k, are defined as int (I4) instead of smallint (I2). However, the summarized total never exceeds the value an int can hold.



I ran into a problem on the insert, however, with SSIS telling me it couldn't insert an I8 value into an I4 table field. I discovered the metadata for the summed totals had automatically been set to bigint (I8), and the mapping was failing.



I didn't see a way to change that metadata within the Aggregation task itself, so I added a Data Conversion task to convert the totals to four-byte signed integers and enable the mapping. Was that the proper workaround?

View 1 Replies View Related

Transact SQL :: Adding Results Of Query To Another Query Via Dynamically Added Columns

Jul 30, 2015

For each customer, I want to add all of their telephone numbers to a different column. That is, multiple columns (depending on the number of telephone numbers) for each customer/row. How can I achieve that?

I want my output to be

CUSTOMER ID, FIRST NAME, LAST NAME, TEL1, TEL2, TEL3, ... etc

Each 'Tel' will relate to a one or more records in the PHONES table that is linked back to the customer.

I want to do it using SELECT. Is it possible?

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

hotelid
hotelname
hotelstate
hotelcity
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.

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

CA
hotel2 San Fransisco xxx-xxx-xxxx




How can I group by without Aggregation ?

View 5 Replies View Related

Aggregation Query

Feb 21, 2003

Hi.
i have a problem that related to aggregation functions.
i have a table (requests)that consist with the following fields:event_id, request_type,data.
i want to present a report that for each event_id it will show the number of records where the request_type value is:"VMR".

i wrote the following query-
SELECT event_id, Count(request_type)
FROM requests
WHERE ((requestType)="VMR")
GROUP BY event_id

the problem is that:if event_id don't has a record with the value "VMR" it will not be showen in the report and the goal is to present those kind of events with the value 0.
who can i make it happen.

thanks.

View 6 Replies View Related

SQL Aggregation Poser

Jul 20, 2005

Can anyone help me with an problem I have come across in my databasedesign.I have a primary table and a related table with 3 child records (eachwith a numeric field). I require a query to return the primary keyfrom the main table and the PRODUCT (i.e. all numeric valuesmultiplied together) of the three child records, much like a SUM wouldadd them together.Any help would be gratefully received!Tony.

View 3 Replies View Related

Sum And Aggregation In A Matrix

Jun 14, 2007

Hello,



I have a matrix where I count number of employees per country, per product line.

The calcul is a simple sum :

(sum(Fields!ID__T_E_Employee_Distinct.Value)).



My report is correct at week level:

March 2007

W9 W10 W11 W12 W13

Product_Line1 17 17 17 17 17

Country Product_Line2 4 4 5 5 5

Product_Line3 25 25 26 25 25

Product_Line4 12 12 12 12 12



However my report is false when I drill-up to month level.



I obtain:

March 2007

Product_Line1 85

Country Product_Line2 23

Product_Line3 126

Product_Line4 60



but I want to obtain:

March 2007

Product_Line1 17

Country Product_Line2 5

Product_Line3 26

Product_Line4 12



For information, my datasource is an OLAP cube.



I have tried to add scope in the sum function but it doesn't work...

Moreover the sum is mandatory for me at week level, but I can have max of the sum at month level.

How to do max of sum in SSRS (I can have only one aggragation function in an expression).



Any help is welcome.



Thanks.

Guillaume.



View 2 Replies View Related

Problem With An Aggregation

Aug 6, 2007

Hi,

I am new to the reporting services and I've been working on problem in one of my reports all day long and after 8 hours of frusturation I decided ask for a profesional help.

Ok here is my problem: I have a report that calculates the amount of meetings with our clients. The dataset contains an activity_id field that we assign for each our meetings with our clients. SSRS counts these meetings and shows it in a drilldown enabled report. Everything seems fine on the report except that someof the activities involves few different clients and SSRS is not counting the activities multiple times in region drilldown as there is only one activity id associates in that region even though it contains different companies. And I want those companies to be calculated in too.

From the crude drawing below I wanted to explain my dilemma visually. As it can be seen the total number of meetings we had is actually 40. But as we had 3 activities that involves more than 1 clients it only gives 37 as a count. I would like to know is there a way to make the report count the same activity multiple times if activity_id is associated with more than one clients.


I hope I managed to explain my problem

**********************************************************************************************
Manager Region Market Company Meeting Detail

+Manager 1 (9 meetings)

+Manager 2 (37 meetings)

- West (37 meetings)







-Denver (37 meetings)



+Company 1 (5 meetings)
+Company 2 (2meetings)
+Company 3 (2meetings)
+Company 4 (3meetings)
+Company 5 (0meetings)
+Company 6 (0meetings)
+Company 7 (5meetings)
+Company 8 (1meetings)
+Comapny 9 (19meetings)
+Company 10 (3meetings)
Total (40 meetings)

View 5 Replies View Related

Aggregation Usage

Apr 10, 2008

I know we can design Aggregation usage for a dimension within a cube. For example, you can choose Full , None, Unrestricted and Default method. I assume this is the Global settings.

Is it possible that Ican do the same thing for the attributes within a dimension? I cannot find such property in attributes. Does this means we can't control which attributes we want Analysis Services to aggregate in a dimension?

View 6 Replies View Related

Aggregation Table

Jul 3, 2007

Hi all



i need to create aggregation table from 2 tables group by date, any one have any idea how to create it by using SSIS



thanks & regards

View 1 Replies View Related

Smart Aggregation

Jan 1, 2007

I'm having problems implementing the following in reporting services 2005.

My hierarchy looks like this (just to illustrate the problem...):

University->Student->Exam

My query returns the following fields:

University,Student,StudentPayment,ExamName,ExamScore

I need to create a report that will show the hierarchy and to smartly aggregate the StudentPayment to both the Student and the University levels.

The problem is that the StudentPayment field is being multiplied by the number of exams in the upper level aggregation.

If only I could set the granularity level of the StudentPayment measurement...

Note that I don't have access to the query, so I can't change anything on that front.

Thanks,

Efi

View 6 Replies View Related







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