Adding Columns Of Data In An Aggregate Query
Mar 14, 2001
Ok so I need to write a query that finds the lowest grade out of a group of students (by a class number), and identifies it by a student id #.
Here's what I'm trying:
select min(gr.grade), gr.stu_id
from dbo.class cl, dbo.grade gr
where (cl.class = 2) and (abs(cl.stu_id) = abs(gr.stu_id))
group by gr.stu_id
but unfortunetly it returns the lowest grade for each student in class 2, and not the single lowest grade in class 2 and the stu_id (student ID).
I'm new to SQL and could really use the help.
Thanks
View 1 Replies
ADVERTISEMENT
Aug 28, 2015
I have a a Group By query which is working fine aggregating records by city. Â Now I have a requirement to focus on one city and then group the other cities to 'Other'. Â Here is the query which works:
Select [City]= CASE WHEN [City] = 'St. Louis' THEN 'St. Louis' ELSE 'Other Missouri City' END, SUM([Cars]) AS 'Total Cars'Â
From [Output-MarketAnalysis]
Where [City] IN ('St. Louis','Kansas City','Columbia', 'Jefferson City','Joplin') AND [Status] = 'Active'
Group by [City]
Here is the result:
St. Louis 1000
Kansas City 800
Columbia 700
Jefferson City 650
Joplin 300
When I add this Case When statement to roll up the city information it changes the name of the city to 'Other Missouri City' however it does not aggregate all Cities with the value 'Other Missouri City':
Select [City]= CASE WHEN [City] = 'St. Louis' THEN 'St. Louis' ELSE 'Other Missouri City' END, SUM([Cars]) AS 'Total Cars'Â
From [Output-MarketAnalysis]
Where [City] IN ('St. Louis','Kansas City','Columbia', 'Jefferson City','Joplin') AND [Status] = 'Active'
Group by [City]
Here is the result:
St. Louis 1000
Other Missouri City 800
Other Missouri City 700
Other Missouri City 650
Other Missouri City 300
What I would like to see is a result like:
St. Louis 1000
Other Missouri City 2450
View 5 Replies
View Related
Aug 9, 2007
Hi all,
I have a scenario which I am not able to figure out how to do it better for quite some time.
Assume I have a few rows of data :
RunningID Date WOid
1234 1/23/2007 23
1236 1/24/2007 23
1239 1/2/2007 24
1222 1/4/2007 23
1321 2/4/2007 22
My objective is to merge all RunningID into a single cell when WOid is the same (this will most probably use a "group by" to get the different WOid out). Maybe some aggregate function that can do it as:
select ReturnConca(RunningID, "#") as RunningID_str, max(Date) as MaxDate, max(WOid) as WO from tableXXX
group by WOid
Results:
RunningID_str MaxDate WO
1234#1236#1222 1/24/2007 23
1239 1/2/2007 24
1321 2/4/2007 22
Any advise would be much appreciated.
View 6 Replies
View Related
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
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
May 11, 2006
Hi
I'm am using SQL Server 2005 Express on my home PC.
To practice some of the SQL skills I learned on a course I was recently on, I am trying to build a mini database which I can run some queries from.
I have created some tables and added columns to these tables. However, I cannot find how to add data to these columns.
I'm sure it's pretty simple but I just can't find it! Can anyone help??
Cheers!
View 4 Replies
View Related
Jul 23, 2005
I'm working on a fairly complex query, and to avoid exploding my brainany further, I'm going to keep this simple with an example of what I'mtrying to do...Let's say you had a table of customers and a table of ordersAnd you had data that looked like this:CustomersCustID CustName-------------------------------1 Billy2 JohnOrdersCustID Amount PaidYN-------------------------------1 $2 12 $3 01 $4 11 $2 02 $5 0And you wanted a report that looked like this:Customer TotalPaid TotalUnpaid------------------------------------------Billy $6 $2John $0 $8How would you write a query to generate this report?
View 2 Replies
View Related
Sep 3, 2015
Is there any way or option to get the all columns of dataset added to table when we add a table in data region. It will take lot of time to add one by one and also there are chances to add one column ore than once.
View 7 Replies
View Related
Jun 13, 2014
I have a simple query like:
select max(PTR_DATE)
from MPR_portfolio_transactions
group by PTR_SYMBOL
and this is working fine, but if I add an extra column with another field like:
select max(PTR_DATE) , PTR_SHAREBALANCE
from MPR_portfolio_transactions
group by PTR_SYMBOL
Then I get an error message like:
Column 'MPR_portfolio_transactions.PTR_SHAREBALANCE' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
How can I get round to this?
View 6 Replies
View Related
Feb 18, 2008
Hi all,
I am trying to aggrgate Values on three columns Customer , OrderDate and Product thorough SSIS. It gives me following masseges and works very very slow.
Before Aggregation I sort the Data by Customer , OrderDate & Product.
The Aggregate transformation has encountered 4085 key combinations. It has to re-hash data because the number of key combinations is more than expected. The component can be configured to avoid data re-hash by adjusting the Keys, KeyScale, and AutoExtendFactor properties.
The Aggregate transformation has encountered 25797 key combinations. It has to re-hash data because the number of key combinations is more than expected. The component can be configured to avoid data re-hash by adjusting the Keys, KeyScale, and AutoExtendFactor properties.
The Aggregate transformation has encountered 253973key combinations. It has to re-hash data because the number of key combinations is more than expected. The component can be configured to avoid data re-hash by adjusting the Keys, KeyScale, and AutoExtendFactor properties.
The Aggregate transformation has encountered 2000037key combinations. It has to re-hash data because the number of key combinations is more than expected. The component can be configured to avoid data re-hash by adjusting the Keys, KeyScale, and AutoExtendFactor properties.
Please help me...
Thank you
Balwant.
View 12 Replies
View Related
Aug 5, 2015
How can I aggregate this result into 1 row? (I got it from a UNION ALL)
Article        Assort1   Assort2
50095811Â Â Â K1Â Â Â Â Â Â Â Â Â Â Â NULL
50095811Â Â Â NULLÂ Â Â Â Â Â Â Â K3
I would like to have
Article        Assort1   Assort2
50095811Â Â Â K1Â Â Â Â Â Â Â Â Â Â Â K3
View 3 Replies
View Related
Jun 1, 2006
Hi,
Can anyone from MS exaplain why the AGGREGATE component doesn't allow you to select MIN/MAX when the column is DT_STR/DT_WSTR?
Thanks
Jamie
View 12 Replies
View Related
Sep 24, 2007
Let me start with saying thanks to all of you who have helped me (I'm a SQL newbee after doing OO for the past 12+ years)
I need to do several aggregates on multiple columns, with each column having different SELECT Criteria.
Sample Data:
Dept Project Cost CostFlag Schedule ScheduleFlag
D1 D1P1 495 1 135 3
D1 D1P2 960 2 70 2
D1 D1P3 1375 3 105 2
D1 D1P4 1050 2 160 3
D1 D1P5 1890 3 40 1
D2 D2P1 650 1 155 3
D2 D2P2 890 2 125 2
D2 D2P3 1235 3 85 1
D2 D2P4 430 1 140 3
D3 D3P1 1960 3 45 1
D3 D3P2 1490 3 85 1
D3 D3P3 1025 2 135 3
D3 D3P4 615 1 100 2
D3 D3P5 270 1 70 1
D3 D3P6 815 2 155 3
I need to calculate MEAN (average), Standard Deviation, Variance, Range, Span & Median for each data column (Cost, Schedule in the test data), where each data column has different selection criteria. I have the calculations working for each column individually (e.g. funcCalcCost, funcCalcSchedule), but I need to return the calculated values as a single data set:
SELECT Dept, Project, AVG(Cost) as Cost_Mean, MAX(Cost) - MIN(Cost) as Cost_Range, .......
WHERE CostFlag = @InputParameter
GROUP BY Dept, Project
The code above works great - but only for a single column. I need to return a dataset like this:
Dept Project Cost_Mean Cost_Range
D1 D1P1 495 135
D1 D1P2 960 70
D1 D1P3 1375 105
I need to return a dataset like this:
Dept Project Cost_Mean Cost_Range Schedule_Mean Schedule_Range
D1 D1P1 495 135 100 28
D1 D1P2 960 70 42 12
D1 D1P3 1375 105 91 38
I also have working code calculate the MEDIAN (what a pain that was, thank god I found a code example to get me going on the MEDIAN)
Thanks!
View 7 Replies
View Related
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
Jul 6, 2015
getting result as given below.
Input:
id Name Data
101 AA PQ102BBRAJAKIRANBUBLU
102 BB RS101AAEROJUCHALABAGUNDI
103 CC TU104GGANDICANKILLANYONE
OUTPUT:
id Name Data
101 AA 101AAEROJUCHALABAGUNDI
102 BB PQ102BBRAJAKIRANBUBLU
103 CC
View 4 Replies
View Related
May 13, 2008
Hi,
I have 3 tables:
Table SLA: SLA_code, SLA_name
Table industry: Industry_code, Industry_name
Table14: SLA_code_origin, SLA_code_destination, Industry_code, Freq
I would like to query to get in one table:
SLA_name for DESTINATION, Industry_name, Freq
Note: SLA_code contains the same codes as SLA_code_origin and SLA_code_destination. All are the same codes in column oringin means the From point and colum destination the To point.
How can I query to get the Freq and Industry for those destination SLAs?
I have make a relationship between:
Table SLA.SLA_code with Table14.SLA_code_destination and
Table_industry.Industry_code with Table14.Industry_code
But I am not sure if just a
SELECT SLA.SLA_name, Industry.Industry_name, Table14.Freq
FROM SLA, Industry, Table14
would select the SLA_destination and not the SLA_origin.
Thanks very much in advance,
Maria
View 1 Replies
View Related
Jan 2, 2007
Can we do this?
Adding more columns in a matrix report that don€™t
belong to the columns drilldown dimensions€¦
That is, for example, having the following report:
Product Family
Product
Country City Number of units sold
Then I
would add some ratios, that is, Units Sold/Months (sold per month) and other that
is the average for Product Family (Units Sold/Number of Product Family), for putting an example€¦ some
columns should be precalculated prior to the report so do not get into it, the
real problem I don€™t see how to solve is adding one or two columns for showing
these calculated column that doesn€™t depend on the column groups but they do
for the rows groups€¦
Any guidance
on that?
The only
way I am seeing by now is to set it as two different reports, and that is not
what my client wants€¦
Many
thanks,
Jose
View 4 Replies
View Related
Jan 11, 2004
What is the SQL syntax for adding a column to an already created DB table?
View 4 Replies
View Related
Jun 10, 2008
I have sales for a store per day, I need to add that sales for the day so say on the 4th of the month it has total sales of that day plus all previous days.
View 2 Replies
View Related
Jul 31, 2006
Hi
I am quite new to the delights of SQL and wondered if some professional could suggest the best approach to tackling the following problem:
If I had the following table:
Runner Result1 Result2 Result3 Result4
Dave 4 4 3 2
Phil 2 3 3 3
Derek 2 4 6 7
And I wanted to add and display a column that would display “best 3 total results�, such as:
Runner Result1 Result2 Result3 Result4 Best3
Dave 4 4 3 2 11
Phil 2 3 3 3 9
Derek 2 4 6 7 17
Any help would be really appreciated.
I would be using asp.net vb.net and sqlserver2003
View 14 Replies
View Related
Mar 5, 2007
Is there a way to add two columns (with numbers) that have already been aliased? I summed the two columns separately, and also want to display a third colum that show the sum of the two already summed columns. Any help is appreciated. thanks.
View 1 Replies
View Related
Jul 12, 2007
Hi, hope someone can help me with this, I have something like this:
sum(per1) as period1, sum(per2) as period2, sum(per3) as period3
And I want to add all the periodX toghether, how can I do it?
On my real SQL query the SUM statement is more complex, with some cases and such.
Also can I divide this result by a number?
View 11 Replies
View Related
Jan 25, 2008
I have this code and it should bring back code for the month i put in in the set statement. When i put in say may i need to add up numbers for all months prior in the total projections call em and im lost
Declare @Division as Varchar(15)
Declare @Date as Varchar(7)
Set @Division = 'Del Sol'
Set @Date = '05/2008'
Select [Month], Sum(Projection) as ProjectionTotal
From Reporting.dbo.RetailSalesComparison_ProjectionsView PV
Inner Join DelsolNet2.dbo.StoreGroup SG on PV.StoreID = OrderStoreID
Where @Division = Case DivisionID When 10 then 'Del Sol'
When 11 then 'Cariloha'
When 12 then 'Sol Kids' else 'Notta' End
and Month([Month]) = Left(@Date,2) and Year([Month]) = Right(@Date,4)
Group By [Month]
View 2 Replies
View Related
Dec 28, 2011
I have get 3 columns in a select query. how can show these 3 columns in row wise.
View 2 Replies
View Related
Nov 20, 2000
I am familiar with the ALTER TABLE syntax that can be used to add columns to an existing table, but the columns are appended to the end of the table. Enterprise Manager allows you to insert a column in the desired place in the table, but I suspect that behind the scenes it creates a temp table with the new structure, drops the old version of the table and renames the temp table to the orginal table name (I could be wrong on this).
Is there a way to insert a new column in a specific place in a table using SQL rather than EM?
View 1 Replies
View Related
Jan 17, 2012
I have a table with 6 columns. which we can call a, b, c, d, e, f. What I want to achieve is to put data in column d and e and then split this result in column f.
The data I want to put in column d and e is already exported and executed from a table called exp_data, which is from a period of november.
So this is what i have so far but is not working:
update split_table set d =
select amount from exp_data
where period = '1111'
and exp_data.account = split_table.b
and exp_data.company = split_table.a
The error I get is incorrect syntax near select. Fixed the issue by adding a parenthesis before the select until the end...
View 6 Replies
View Related
Apr 21, 2014
I have a report that looks like the following
NameCity Client NoAccount No Balance
SmithSydney 1234561258792 3.95
JonesMelbourne 2589641000657 9.54
BrownPerth 9876541000879 5.46
BrownPerth 9876541000880 7.51
WhiteSydney 6548521007562 10.65
HOWEVER I need it to look like the following:
NameCity Client NoAccount No BalanceAccount No Balance
SmithSydney 1234561258792 3.95
JonesMelbourne 2589641000657 9.54
BrownPerth 9876541000879 5.461000880 7.51
WhiteSydney 6548521007562 10.65
The requirement is that if the Client No is the same that the Account No and Balance appear on the same row but just additional columns. There is no restriction on how many extra columns there would be.
Once the query is working it needs to go into Visual Studio so that the report can be set up as a subscription.
View 1 Replies
View Related
Dec 12, 2006
I have a LIVE SQL 2000 database. I am trying to add some new columns to a table. My question is will I need to take the database off-line to perform this operation? I have replicated it to a sample table if I can't.
KL Hutch
View 1 Replies
View Related
Sep 27, 2007
Hi!
I have one table with thousands of records. Now, I have to add one more column to that table. And this new column will contain concatination of two columns in that same table. Is there any command to do this?
My table has two columns as 'file', 'extn', now i want to add 'fullname' column to that table, and it will contain the concatinated value of 'file' and 'extn'. If the 'file' field has one record as "myfile", 'extn' has one record as ".doc", I want to insert "myfile.doc" in the new column 'fullname'. Like this my table has thousands of records. How can i do this? Please help me! Thanks in advance!
View 9 Replies
View Related
Jul 23, 2005
HiI have a table that currently has 466 columns and about 700,000records. Adding a new DEFAULT column to this table takes a long time.It it a lot faster to recreate the table with the new columns and thencopy all of the data across.As far as I am aware when you add a DEFAULT column the followinghappens:a) The column is added with a NULL propertyb) Each row is updated to be set to the DEFAULT valuec) The column is changed to NOT NULL.However, adding the column as NOT NULL with the DEFAULT seems to take alot longer than if I do steps a) - c) separately.When I say a long time, adding just a single DEFAULT column takesaround 6 hours. Surely it should not take this long?There is a trigger on this table but disabling this does not seem tomake much difference.Can anybody give me any advice on the use of DEFAULT columns please?When should they be used, benefits, disadvantages, alternatives etc.Also should it really take as long as it is taking or is there aproblem with my setup?If I am honest I can't see why DEFAULT columns should be used as thevalues could always be inserted explicitly via the applicationThanks in Advance.Paul
View 5 Replies
View Related
Sep 22, 2007
I have three tables.
Member(name, address, ID)
Loan(ID, startdate, amount)
Deposite(ID, startdate, amount)
I wanna create a report which look like this.
ID MembersName startdate address etc
Member can be either borrower or a depositor.
I'm thinking of using inner join. Can anyone help me to write the query?
Thanks
View 3 Replies
View Related
Sep 3, 2006
Dear All,
I have a requirement, in which i have to add columns dynamically depending the records of a master table in sql server 2000.
Pl, do help me
View 2 Replies
View Related
Jan 3, 2008
we have a database hosted online on sql server 05. at work, we have a sql 2000 query analyzer on a machine, and sql 2005 management studio on a machine. both machines can connect to the database, both can pull up the data from a certain table. but there are two columns that display info in query analyzer that show up as empty on management studio. not null, just empty.
any ideas why?
View 1 Replies
View Related