What's the best way to include an amount sum in a query if I also need the individual amounts? For example, I need the following columns:
order number
order amount
total amount
I tried using "with cube ", but the total number of columns in the query exceeds the allowable limit of 10.
Hi,Suppose I have a table containing monthly sales figures from my shopbranches:Branch Month Sales-----------------------London Jan 5000London Feb 4500London Mar 5200Cardiff Jan 2900Cardiff Feb 4100Cardiff Mar 3500The question I am trying to ask is this: in which month did each branchachieve its highest sales? So I want a result set something like this:Branch Month----------------London MarCardiff FebI can do a "SELECT Branch, MAX(Sales) FROM MonthlySales GROUP BY Branch" totell me what the highest monthly sales figure was, but I just can't figureout how to write a query to tell me which month corresponded to MAX(Sales).Ideas anyone?Cheers,....Andy
View 5 Replies View Related-- The 3rd query uses an incorrect column name in a sub-query and succeeds but rows are incorrectly qualified. This is very DANGEROUS!!!
-- The issue exists is in 2008 R2, 2012 and 2014 and is "By Design"
set nocount on
go
if object_id('tempdb.dbo.#t1') IS NOT NULL drop table #t1
if object_id('tempdb.dbo
[code]....
This succeeds when the invalid column name is a valid column name in the outer query. So in this situation the sub-query would fail when run by itself but succeed with an incorrectly applied filter when run as a sub-query. The danger here is that if a SQL Server user runs DML in a production database with such a sub-query which then the results are likely not the expected results with potentially unintended actions applied against the data. how many SQL Server users have had incorrectly applied DML or incorrect query results and don't even know it....?
do i need to nest a query in RS if i want a calculated column to be compared against a multi value variable? It looks like coding WHERE calcd name in (@variable) violates SQL syntax. My select looked like
SELECT ... ,CASE enddate WHEN null then 1 else 0 END calcd name
FROM...
WHERE ... and calcd name in (@variable)
When I run query in excel it gives result with different column sequence. The same query gives result with different column sequence when used in query analyzer or VBA Macro. E.g., Select * from ABC.
result in Excel 2003 SQL OLE DB query
col-A col-B col-C
values...
Result with Query Analyzer and VBA Macro
col-c col-B col-A
values...
I need to name make the name of a column the same as the name of a table from the result of a sql query.. here is the assignment question below..I can't figure out how to get the name of the table to be inputed as the column name..
Write a script that uses dynamic SQL to return a single column that represents the number of rows in the first table in the current database. The script should automatically choose the table that appears first alphabetically, and it should exclude tables named dtproperties and sysdiagrams. [highlight=#ffff11]Name the column CountOfTable, where Table is the chosen table name.[/highlight]
Hint: Use the sys.tables catalog view.
I can figure out the rest. and actually have alredy done it, but i cannot figure out how to do the part that is highlighted above. I looked at lots of things on google to figure it out but no luck..Can some just give me some directlon or an example..
I have a column colC in a table myTable that has a value (e.g. '0X'). The position of a non-zero character in column colC refers to the ordinal position of another column in the table myTable (in the aforementioned example, colB).
To get a column name (i.e., colA or colB) from table myTable, I can join ("ON cte.pos = cn.ORDINAL_POSITION") to INFORMATION_SCHEMA.COLUMNS for that table catalog, schema and name. But I want to show the value of what is in that column (e.g., 'ABC'), not just the name. Hoping for:
COLUMN_NAME Value
----------- -----
colB 123
colA XYZ
I've tried dynamic SQL to no success, probably not executing the concept correctly...
Below is what I have:
CREATE TABLE myTable (colA VARCHAR(3), colB VARCHAR(3), colC VARCHAR(3))
INSERT INTO myTable (colA, colB, colC) VALUES ('ABC', '123', '0X')
INSERT INTO myTable (colA, colB, colC) VALUES ('XYZ', '789', 'X0')
;WITH cte AS
(
SELECT CAST(PATINDEX('%[^0]%', colC) AS SMALLINT) pos, STUFF(colC, 1, PATINDEX('%[^0]%', colC), '') colC
[Code] ....
I'm having a bit of a trouble explaining what I'm trying to do here.
I have 3 "source" tables and a "connecting" table that I'm going to use
tblContacts - with contactID, ContactName etc
tblGroups - with GroupID, GroupName
tblSubGroups - with SubGroupID, GroupID and SubGroupName (groupID is the ID for the parent Group from tblGroups)
They are related in a table called
tblContactsGroupConnection - with ContactID, GroupID and SubGroupID
One contact can be related to many subgroups.
What I want is a list of all contacts, with their IDs, names and what groups they are related to:
ContactID, ContactName, [SubGroupName1, SubGroupName2, SubGroupName3]
ContactID, ContactName, [SubGroupName1, SubGroupName3]
ContactID, ContactName, [SubGroupName3]
I'm sure there's a simple solution to this, but I can't find it. Any help appreciated. :)
Kirikiri
Can someone tell me how to sum values in a column from a resultant Query. For example, consider this query which in partcalls a view named "Rpt_OverallIndMarket"
select * from Rpt_OverallIndMarket order by MarketId,Year,Quarter
The resultant query from the previous query will contain a column named TotalSquareFeet. How do I sum all of the TotalSquareFeet values inthat column?
Hi, I'm pretty sure that I've missed something obvious but I can't think of what it is, I need to get rows where "forid" is 0 and 1 but I can't think of how to do it, I tried:
SELECT * FROM `events` WHERE `forid` = '0' AND `forid` = '1'
But that query isn't correct.
Appreciate any help, thanks.
My XML column is like below.
<rs:CheckPointExpressIDNotRequired xmlns:rs="urn:release-schema">
<rs:IsJavaRuling>True</rs:IsJavaRuling>
<rs:IsGeoPolitical>True</rs:IsGeoPolitical>
<rs:IsConsentDecree>True</rs:IsConsentDecree>
<rs:HasThirdPartyBits>False</rs:HasThirdPartyBits>
<rs:MandatoryLicenseAgreement>Verified</rs:MandatoryLicenseAgreement>
</rs:CheckPointExpressIDNotRequired>
My query is below.
SELECT
ref.value('IsJavaRuling[1]', 'varchar(36)') as [IsJavaRuling]
FROM REQUESTCOMPLIANCE CROSS APPLY xml.nodes ('//CheckPointExpressIDNotRequired') R(ref)
order by lastmodifieddate desc
My query runs successfully but I am not getting any results back however there are many rows to be returned. Can you tell me what is wrong with the query?
Thanks,
Koti
Not sure if this is possible, but maybe. I have a table that contains a bunch of logs.
I'm doing something like SELECT * FROM LOGS. The primary key in this table is LogID.
I have another table that contains error messages. Each LogID could have multiple error messages associated with it. To get the error messages.
When I perform my first select query listed above, I would like one of the columns to be populated with ALL the error messages for that particular LogID (SELECT * FROM ERRORS WHERE LogID = MyLogID).
Any thoughts as to how I could accomplish such a daring feat?
Is it possible to use a column name variable in a Select Statement for a column name?For example I have a dropdown with FName,LName,Phone and a text box. The user can select the field to search and the criteria will go into the text box. The problem is the select doesn't like a variable for the field name. I have tried both a standard variable and a Case statement (see below). This is being used in a Stored Procedure with MSSQL. The actual select is much more complicated than this but it gets the point across. Thanks for your help in advance@Field as varchar( 50),@Value as varchar (50)SELECT *FROM customersWHERE @Field = @ValueORSELECT *FROM customersWHERE CASE WHEN @Field = 'Fname' THEN Fname = @Value END, CASE WHEN @Field = 'Lname' THEN Lname = @Value END, CASE WHEN @Field = 'Phone' THEN Phone = @Value END;
View 1 Replies View RelatedI have 2 tables, my vehicle data table and my config table. I need a
query to join them by a datarow and a data column. Heres my tables...config table--------------------id name type--------------------1 make varchar2 model varchar3 color varcharveh table--------------------------id make model color--------------------------1 chevy s10 white2 ford ranger silver2 chevy blazer brownrecordset needed for veh.id=1---------------------------id name type value---------------------------1 make varchar chevy2 model varchar s103 color varchar white Thanks for any helpRyan
Hi I have the following problem. I am trying to get some data from a database which matches the name in a session from a previous page:e.g. SqlCommand menubar = new SqlCommand("Select pernme from Person where pernme = " + (string)Session["tbname"], sqlConn); SqlDataAdapter dataAdapter5 = new SqlDataAdapter(); dataAdapter5.SelectCommand = menubar; DataSet dataSet5 = new DataSet(); dataAdapter5.Fill(dataSet5); DataTable selcartest4 = dataSet5.Tables["table"]; if (selcartest4.Rows.Count != 0)The session is called tbname and in that session is a users name however insetad of doing the nornal thing and retrieving the data in the sql database table matching that name it comes up with the following error message:System.Data.SqlClient.SqlException: Invalid column name 'jamie'this is weird as the 'jamie' is the name in the session from the previous page and in fact not a column name at all the column name is pernme I am totally stuck any help would eb great thanksJ
View 2 Replies View RelatedSimple example would look like that in MS SQL
SELECT 'a' AS (SELECT language_name FROM language WHERE language_id = 1)
So that the display is
English
a
as we assume that
SELECT language_name FROM language WHERE language_id = 1
returns only English
I have tried to that with a variable but it does not work
declare @that as varchar(15);
set @that = (select language_name_u from language where language_id = 1);
select 'a' as @that
LOL
I just tried another way as i was going to post
declare @that as varchar(15);
set @that = (select language_name_u from language where language_id = 1);
select 'a' as "@that"
and it worked!
Posting anyway so people might answer with a better solution with no variable
Thanks a lot
Mordan
Hi all,
Can somebody tell me how I can pass column name of a table to a stored proc.
Here's something close to what I'm trying to do....
Declare @colname varchar(30)
set @colname = "TITLE"
select name, id
from contacts
where contacts.@colname = "TITLE"
Regards
Uday
I am trying to parse a query column that has both a city name and state code within it. What I would like to do is parse the queryed column and seperate them by the ",". Can someone please help?
example:
Select Name, Address1, Left(Address2, ",") as City, Right(Address2, ",") as State
From tblPersonalInfo
I am trying to develop this query in MSSQL but am having a problem with the syntax.
I don't know why but the query is breaking on 'Invalid Column Name A1'.
Here is my query.
Code:
SELECT
Groups.GroupID,
Sum(Stages_On_Route.Distance) AS Miles_Covered,
Groups.Group_Name
FROM Groups
INNER JOIN ((Route INNER JOIN Departure ON (Route.GroupID=Departure.GroupID)
AND (Route.RouteID=Departure.RouteID))
INNER JOIN Stages_On_Route ON Route.RouteID=Stages_On_Route.RouteID)
ON Groups.GroupID=Departure.GroupID
GROUP BY Groups.GroupID,
Groups.Group_Name
HAVING (((Groups.GroupID)="A1"));
is there a way to sql query via a spesific column number
'get from table A row 1 column 1' and then 'get from table A row 2 column 3'
and so on
thanx
I have a database with a zip code column. I want to flag all rows that have letters in that column (a-z). How could I construct the where clause?
Is there a better alternative to:
WHERE ZipCode LIKE '%a%' OR ZipCode LIKE '%b%' ...
Hello, everyone:
I have a table like:
Col1Col2
1A
2B
1D
1P
2F
2W
How to query this table to return by Col1 like
Col1Col2
1A,D,P
2B,F,W
Thanks a lot
ZYT
need help ,
i have a table called "Loans" where i need to compute a column i.e. NoofDays based on which other calculation like interest calculation needs to be done.
my query goes like this
"select datediff(dd,VDate, MDate) as NoOfDays ,NoOfDays * Principal * Rate /100 * 365 from Loans".
if i run the above query it says
"Invalid column name 'NoOfDays'".
this executes fine if i use Access but not in SQL Server.
can anybody say what might be the problem and how i can solve it.
regards
Rajesh :)
Hi all,
I have a problem writing a query to change row to column with some conditions and computations. It's appreciated if you guys can give some ideas.
Below is the sample data:
CREATE TABLE Item (Line int, Code nvarchar(8),Price decimal, Check nvarchar(1))
INSERT INTO Item VALUES (1,'001',200 ,'Y')
INSERT INTO Item VALUES (2,'002',300 ,'Y')
INSERT INTO Item VALUES (3,'003',500 ,'Y')
INSERT INTO Item VALUES (4,'004',1000,'N')
INSERT INTO Item VALUES (5,'005',2000,'N')
The expected result :
Line with check = 'N' must be converted to column(s).
Line Code Price Item004 Item005
1 001 200 1000 * (200/200+300+500) 2000 * (200/200+300+500)
2 002 300 1000 * (300/200+300+500) 2000 * (300/200+300+500)
3 003 500 1000 * (500/200+300+500) 2000 * (500/200+300+500)
Note : I would like to avoid using any cursors if possible.
Thanks in advance.
cheers,
erwine
... sql is fun...
Hi all,
This is my result set. And before
select
dbo.fn_ResolveClubID(clubid) as 'Team',
dbo.fn_ResolveMatchID(matchid) as 'Match',
convert (varchar, max(matchDate), 111) as 'Date',
--dbo.fn_ResolveTimeValue(@SessionID, max(TimeOnPitch)) as 'Time On Pitch',
-- For Extrpolation
case when (sum(TimeonPitch) <> sum(CopyTimeonPitch)) then dbo.fn_ResolveTimeValue(@SessionID, sum(CopyTimeonPitch))
else dbo.fn_ResolveTimeValue(@SessionID, sum(totalperiod)) end as 'Time On Pitch',
cast(cast(sum(totalPassCount) as int) as int) as 'Passes',
cast(cast(sum(SuccessfulPasses) as int) as int) as 'Successful Passes',
cast(cast(sum((totalPassCount - SuccessfulPasses)) as int) as int) as 'Unsuccessful Passes',
cast(ISNULL(cast((sum(SuccessfulPasses)/NULLIF(sum(totalPassCount), 0)) * 100 as numeric(7,1)), 0) as numeric(7,1)) as '% successful passes',
cast(ISNULL(cast(((sum(totalPassCount) - sum(SuccessfulPasses))/NULLIF(sum(totalPassCount), 0)) * 100 as numeric(7,1)), 0.0) as numeric(7,1)) as '% unsuccessful passes',
cast(cast(sum(forwardPassCount) as int) as int) as 'Forwards',
cast(cast(sum(forwardPassSuccess) as int) as int) as 'Forwards Successful',
cast(cast(sum((forwardPassCount - forwardPassSuccess)) as int) as int) as 'Forwards Unsuccessful',
cast(ISNULL((sum(forwardPassSuccess)/NULLIF(avg(forwardPassCount), 0)) * 100, 0.0) as numeric(7,1) ) as 'Forwards % successful',
cast(ISNULL(cast(((sum(forwardPassCount) - sum(forwardPassSuccess))/NULLIF(sum(forwardPassCount), 0)) * 100 as numeric(7,1)), 0.0) as numeric(7,1)) as 'Forwards % unsuccessful',
cast(cast(sum(backwardPassCount) as int) as int) as 'Backwards',
cast(cast(sum(backwardPassSuccess) as int) as int) as 'Backwards Successful',
cast(cast((sum(backwardPassCount) - sum(backwardPassSuccess)) as int) as int) as 'Backwards Unsuccessful',
cast(ISNULL((sum(backwardPassSuccess)/NULLIF(avg(backwardPassCount), 0)) * 100, 0.0) as numeric(7,1)) as 'Backwards % successful',
cast(ISNULL(cast(((sum(backwardPassCount) - sum(backwardPassSuccess))/NULLIF(sum(backwardPassCount), 0)) * 100 as numeric(7,1)), 0.0) as numeric(7,1)) as 'Backwards % unsuccessful',
cast(cast(sum(sidewaysPassCount) as int) as int) as 'Sideways',
cast(cast(sum(sidewaysPassSuccess) as int) as int) as 'Sideways Successful',
cast(cast(sum((sidewaysPassCount - sidewaysPassSuccess)) as int) as int) as 'Sideways Unsuccessful',
cast((sum(sidewaysPassSuccess)/NULLIF(sum(sidewaysPassCount), 0)) * 100 as numeric(7,1) ) as 'Sideways % successful',
cast(ISNULL(cast((sum((sidewaysPassCount - sidewaysPassSuccess))/NULLIF(sum(sidewaysPassCount), 0)) * 100 as numeric(7,1)), 0.0) as numeric(7,1)) as 'Sideways % unsuccessful',
cast(sum(successfulFirstTime + unsuccessfulFirstTime) as int) as 'First Time Passes',
cast(sum(successfulFirstTime) as int) as 'First Time Complete',
cast(sum(unsuccessfulFirstTime) as int) as 'First Time Incomplete',
cast(ISNULL(cast(((sum(successfulFirstTime)/NULLIF((cast(sum(unsuccessfulFirstTime) as int) + cast(sum(successfulFirstTime) as int)), 0)) * 100) as numeric(7,1)), 0.0) as numeric(7,1)) as '% First Time Complete',
cast(ISNULL(cast((sum(unsuccessfulFirstTime)/NULLIF((cast(sum(unsuccessfulFirstTime) as int) + cast(sum(successfulFirstTime) as int)), 0) * 100) as numeric(7,1)), 0.0) as numeric(7,1)) as '% First Time Incomplete',
--dbo.fn_ResolvePlayerID(Select Top 1 PlayerID From @REsu) as 'Top Passer',
Max(TopPasserNumber) as [Top Passer Number],
Max(TopReceiverNumber) as [Top Receiver Number],
cast(cast(cast(sum(headersCount) as int) as int) as int) as 'Headers',
cast(ISNULL(cast((sum(SuccessfulHeaders)/NULLIF(sum(headersCount), 0)) * 100 as numeric(7,1)), 0.0) as numeric(7,1)) as '% successful headers',
cast(ISNULL(cast((sum((headersCount - SuccessfulHeaders))/NULLIF(sum(headersCount), 0)) * 100 as numeric(7,1)), 0.0) as numeric(7,1)) as '% unsuccessful headers',
cast(cast(sum(passReceivedCount) as int) as int) as 'Balls Received',
cast(sum((InterceptionCount + InterceptionsAfterTackle)) as int) as 'Interceptions',
cast(sum(numberOfTouches) as int) as '# touches',
cast(sum(numberOfPossessions) as int) as '# Possessions',
cast(ISNULL(cast((cast(NULLIF(sum(numberOfTouches), 0) as numeric(7,1))/cast(NULLIF(sum(numberOfPossessions),0) as numeric(7,1))) as numeric(7,1)), 0.0) as numeric(7,1)) as 'Avg Touches',
cast(ISNULL(cast(sum(TimeOnBall)/cast(NULLIF(sum(numberOfPossessions), 0) as numeric(7,1)) as numeric(7,1)), 0.0) as numeric(7,1)) as 'Avg Time in Poss',
cast(sum(shortPassCount) as int) as 'Short Passes',
cast(sum(mediumPassCount) as int) as 'Medium Passes',
cast(sum(longPassCount) as int) as 'Long Passes'
-- For Extrapolation
,case when (sum(TimeonPitch) < sum([totalperiod])) then '1' else '0' end as 'ExtraPolated'
from @results Res
where Res.ClubID in (Select ClubID From TrendSelected_Teams Where SessionID = @SessionID)
and ((@excludeGK > -1 and dbo.fn_isGoalkeeper(matchid, playerid) = 0) or
(@excludeGK = -1))
group by Res.clubid, Res.matchid
Before displaying the above columns there are many calculation, updation's etc.
After doing all that i am displaying the above columns.
Here i am showing the results Team wise.
Now i need to merge one more column called player name.
So, already the result set has
Group By Res.clubid, Res.matchid so i cant go for grouping by Playerid, i need result only by ClubID wise,
how can i add one column which should display PlayerID or PlayerName
Thanks
Ganesh
Solutions are easy. Understanding the problem, now, that's the hard part
I have a table like this:
name | purchase amount | purchase date |
Joe--------20.00--------------12/1/06
Joe--------10.00--------------12/1/06
John-------10.00--------------1/15/06
Sam--------15.00--------------5/10/06
John-------20.00--------------11/1/06
Joe--------5.00---------------8/19/07
Sam--------5.00---------------10/8/07
Sam--------10.00--------------4/22/08
Sam--------15.00--------------4/22/08
and what I need is a query that will show name and the sum of purchase amount for specific years. Here is what it would look like:
name | purchase 2006 | purchase 2007 | purchase 2008
Joe 30.00 5.00 0
John 30.00 0 0
Sam 15.00 5.00 25.00
tablename/columnname to query the return values.I want to have this query that returns the table names and column names that contain the given value(s). Business needs dictated that I have to start from given values instead. That is to say we own the apps developed by vendors, and own the dbs, but no middle tier code, nor data dictionary.
So, I started with this
select distinct object_Name(Object_ID) myTbls, [name] from sys.columns where name like '%mycolname%'
and object_Name(Object_ID) not like 'fn%'
I have to guess the possible column name as the starter. I realize if the condition value is not very unique, the return dataset could be huge. It is a performance nightmare and it is useless. So I hope the solution will let me specify, say return the first 1000 matches.
I have a pretty simple SQL query that has two columns that contains only numbers. Is there a way i can add an extra column that simply subtracts the number in one column from number in the other column.I query the data tables rather than have a live link so the .csv file gets overwritten every time i run the query.
View 4 Replies View RelatedHow to run a query that is in the column as text?
View 2 Replies View RelatedI have a data containing salesman who can belong to groups. The groups have "split" values but the splits are not on a percent basis. Can I create a query to get the percentage in a column? Please look at the table below and my attempted query.
Salesman GroupSplitSplitPercent
AG110.33
BG110.33
CG110.33
YG20.50.5
ZG20.50.5
UPDATE Splits
SET SplitPercent = Split / (SELECT SUM(Split) FROM Splits GROUP BY Splits.Group HAVING Splits.Group = ???)
i have one table called Healthmanagement
in that i have column HealthTitles in that there r so many rows..few of them r here:
HealthTitles
---------------------------------
Lung Health
Lung Health: General
Lung Health: Asperic Detailed Modified
Diseases
Diseases: in one generation
Attacks
Attacks: Health Related
i want in ouput all rows but which r with ':' i want after that part only and if the rows r without ':' then remain as it is:
so i want
HealthTitles
---------------------------------
Lung Health
General
Asperic Detailed Modified
Diseases
in one generation
Attacks
Health Related
how can i get that?
thanks for any help.
Hi groupI have a rather peculiar question, and I really don't know how to solvethis within an SQL statement:Given a view (v), that results in:IDX-----------------1a1b2a2c3aI'd like to query the view with something like:SELECT ID FROM v WHERE (X='a' AND X='b') which would result in:ID-----------------1or in another case:SELECT ID FROM v WHERE (X='a' OR X='c')would give:ID-----------------123how can this be done?TIAbernhard--www.daszeichen.chremove nixspam to reply
View 14 Replies View RelatedHi,I have a large table with a 'datetime' column that has date and timevalues in it. The data is in this format:2007-10-02 09:54:00.000The table is called 'profile' and the column 'msgdate'I want to return only rows that match a specific date. So far I havethe following query working:select * from profile where msgdate like '%2007%'This returns all rows that start with '2007'. However I cannot seem toge the syntax that will allow me to return a specific date, e.g.2007-10-02I have researched this, trying all sorts of queries with escapecharacters/sequences because of the dash character, but I cannot getit to return anything. Most of my queries have ran without error, itsjust that no data is returned.James
View 2 Replies View Related