I am retrieving some data from Invoices, Customers and Companies tables as follows, but would like to make the customerName and the Companies.Name as single column such Name and similarly for customerID/companyID and customerCode/companyCode.
Code:
with cte
as
(
selectdistinct i.invoiceNumber, itemID, customers.customerID, Companies.companyID
,SUM(net_weight) as totalWeight, rate
,(select SUM(net_weight) * rate) as amount
The goal is to group all this transactions by Month and Year.
Something like this....
MS030_A MS030_F MS036_A MS036_F MS040_A MS040_F Month Year
3 2 2 2 2 2 1 2006
4 4 7 9 8 9 2 2006
10 10 6 8 8
3 2006
4 4 5 5 3 2 4 2006
5 6 8 3 7 1 5 2006 For just one date column it is pretty straight forward i.e., just do a select count and group by DATEPART ( Mm, DateField) but for multiple columns i am in a total fix....
I am trying to SELECT data based on custom groups of that data. For example and in its simplest form:
SELECT COUNT(*) FROMdbo.People WHERE Current_Status = ‘A’ GROUP BY People_Code
The People_Code is the difficult part. The code represents the building that they work in. However, some buildings have multiple People_Codes. Kind of like multiple departments within a building.
For example:
Building NamePeople_CodeEmployee Count Building A617535 Building B985665 Building C529212 Building C529932 Building C419816 Building D326974 Building D781024 Building E25365
Each building has a main People_code which, for this example, could be any one of the codes for the building. For example: Main code for building C can be 5292 and for building D it can be 7810.
Applying a variation (which is what I cannot figure out) of the SELECT statement above to this table, the result set for Building C must be the combined employee count of all three People_codes and must be represented by the main code of 5292 as a single row. Building D would have a row using code 7810 but will combine the employee count of codes 7810 and 3269.
I built a conversion table that would match up the main code with all of its related codes but just couldn’t seem to make it dance the way I want it to.
People_CodeNameGroupNameGroupPeopleCode 6175Building ABuilding A6175 9856Building BBuilding B9856 5292Building CBuildingCGroup5292 5299Building C AnnexBuildingCGroup5292 4198Building C Floor6BuildingCGroup5292 Etc…
The whole query is much more involved than just the simple SELECT statement used here, but if I can get this to work, I’m sure I can apply it to the full query.
Maybe its a friday, but I can't figure out how to total the returned results so I just get a sum of the 'Items' and 'Total'. I thought it was Compute but that did not work!
I'm pulling data from 2 tables, Order and Order_Item and what Im trying to achieve is to total up how many items were sold and the total. Each one of the rows below are for each order.
I must be doing something wrong, can anyone help?
SELECTsum(Order_Item.Quantity) as Items, sum(Order_Item.Price*Order_Item.Quantity)-[Order].Discount as Total FROM[Order] JOINOrder_Item ON Order_Item.[OrderID] = [Order].[ID] GROUP BY[Order].[ID], [Order].OrderRef, [Order].CustomerID, [Order].ReceivedDate, [Order].Postage, [Order].Discount ORDER BY [Order].ReceivedDate DESC
Items Total ==== ===== 114.9900 113.5920 333.6750 227.1840 567.9600
I always know that the ReportPackId and SupplierPartyIdentifier will be identical for all rows because of the Where condition, and therefore I want all AdditionalPartyIdentifiers to be in columns instead of a new row. Is this possible?
I'm wondering if there is a single statement I can write to pull my data. Let's say my Order table has one field for userId and one field for supervisorId (among other fields) both of which are foreign keys into the Users table where their name, address, etc. are stored. What I'd like to do is to pull all the rows from Order and have a join that pulls the user name and supervisor name from the User table all in one go. Right now I pull all the Orders with just user name joined, and then go back over the objects to add the supervisor name as a separate query.
The reason I'd like to do this is to simplify the objects I'm passing to the GridView by doing a single fetch instead of multiples. I'm using SQL Server, .NET 2.0 and VS.NET 2005.
Hi all,Is this at all possible? In the following query I have mutiple columns in my SELECT statement that each have their own alias. Is it possible that I can use just one Alias for all these columns (such as Address), and if so how is it done?SELECT RTRIM(ISNULL(ta.house_no_flat, '')) as [Target - Flat No.], LOWER(RTRIM(ISNULL(ta.building, ''))) as [Target - Building], LOWER(RTRIM(ISNULL(ta.road_street, ''))) as [Target - Street], LOWER(RTRIM(ISNULL(ta.district, ''))) as [Target - District], LOWER(RTRIM(ISNULL(ta.town, ''))) as [Target - Town], LOWER(RTRIM(ISNULL(ta.county, ''))) as [Target - County], RTRIM(ISNULL(ta.postcode, '')) as [Target - PostCode]ThanksTryst
I've been busy all night searching and reading trying to figure out how I can do the following.
I have a table that tracks user IDs in multiple fields. When I select records from this table I need a way to resolve those ID fields back into user names by referencing the users table. SQL statement thus far...
SELECT A.Username as NameA, B.Username As NameB, FROM theTable, Users As A, Users As B WHERE theTable.UserIDA ???
How do I resolve theTable.UserIDA and theTable.UserIDB back to Users.Username so that the records returned fill the fields NameA and NameB?
Is this at all possible? In the following query I have mutiple columns in my SELECT statement that each have their own alias. Is it possible that I can use just one Alias for all these columns (such as Address), and if so how is it done?
Code:
SELECTRTRIM(ISNULL(ta.house_no_flat, '')) as [Target - Flat No.], LOWER(RTRIM(ISNULL(ta.building, ''))) as [Target - Building], LOWER(RTRIM(ISNULL(ta.road_street, ''))) as [Target - Street], LOWER(RTRIM(ISNULL(ta.district, ''))) as [Target - District], LOWER(RTRIM(ISNULL(ta.town, ''))) as [Target - Town], LOWER(RTRIM(ISNULL(ta.county, ''))) as [Target - County], RTRIM(ISNULL(ta.postcode, '')) as [Target - PostCode]
I have built an Advanced Search page which allows users to select which columns to return (via checkbox) and to enter search criteria for any of the selected columns (into textboxes). I build the SQL statement from the properties of the controls. Works great. My problem is getting my gridview control to play nicely. At first I used a SqlDataReader and bound the gridview to it, thus giving me the ability to run new SQL statements through it (with different columns each time). Worked nicely. But, per Microsoft, sorting can only be done if the gridview is bound to a datasource control like the SqlDataSource. So I wrote the code to handle sorting. No big deal; worked nicely. But I could not adjust the column widths programmatically unless bound to a datasource control like the SqlDataSource. And could not figure out a work around. So, I decided to use the SqlDataSource. Works great. Except, I cannot figure out how to run a new SELECT statement through the SQLDataSource and have the gridview respond accordingly. If I try to return anything other than the exact same columns defined declaratively in the html, it pukes. But I need to be able to return a new selection of columns each time. For example, first time through the user selects columns 1,2,3,4 – the gridview should show those 4 columns. The second time the user selects columns 2,5,7 – the gridview should those 3 columns (and ONLY those 3 columns). Plus support selection and sorting. I am desperate on this. I've burned 2.5 days researching and testing. Does anyone have any suggestions? Thanks, Brad
I have this update statement I am trying to use, to update a table. My problem is if there is no data in the select statement, it still adds number to the columns. How can I have this update statement work to put blank value in if there are no counts?
UPDATE T_AXA_BreakDown_Claims SET [Claim Count Conm] = t2.[Claim Count Conm] FROM T_AXA_BreakDown_Claims t1 INNER JOIN (select
I am currently trying to write a query that pulls a summation of item specific data from sales orders. For simplicity's sake, the column structure can be something like the following...
Item#, PoundsDuringWeekNumber (this would be the current week number out of the 52 weeks in a year and the pounds of the item during that week)
However, those are not going to be the only 2 columns. The idea of the query is that the user would be able to provide the query a date range (say 2 months) and the columns would then morph into the following...
Item#, PoundsDuringWeekNumber (current), PoundsDuringWeekNumber(current - 1), PoundsDuringWeekNumber (current - 2), etc. etc. PoundsDuringWeekNumber(current - 8)
Initial ideas where to create a function to execute the summation of the pounds during the date range of the week in question and execute it across the columns, but with the indeterminable number of columns, the query would not know how many times to execute the function.
I've been busy all night searching and reading trying to figure out how I can do the following.
I have a table that tracks user IDs in multiple fields. When I select records from this table I need a way to resolve those ID fields back into user names by referencing the users table. SQL statement thus far...
SELECT A.Username as NameA, B.Username As NameB, FROM theTable, Users As A, Users As B WHERE theTable.UserIDA ???
How do I resolve theTable.UserIDA and theTable.UserIDB back to Users.Username so that the records returned fill the fields NameA and NameB?
I have a table that holds notes for item's. I'm want to do a select statement where one of my columns assigns a sequential value to each row based on the item number. Would like data to look like this where doc_no would be my row_number function:
item_no seq_no note doc_no ABC 1 blah 1 ABC 2 blahh 1 ABC 3 bla3 1 XYZ 1 more n 2 XYZ 2 another 2 EFG 1 blahhh 3
Below. I have also pasted the current result of this query and the desired result.
Query can be updated to get the desired result as given below?
Query: Select c.OTH_PAYER_ID, c.PAID_DATE, f.GROUP_CODE, f.REASON_CODE, f.ADJUSTMENT_AMOUNT From MMIT_CLAIM_ITEM b, mmit_tpl c , mmit_attachment_link d, MMIT_TPL_GROUP_RSN_ADJ f where b.CLAIM_ICN_NU = d.CLAIM_ICN and b.CLAIM_ITEM_LINE_NU = d.CLAIM_LINE_NUM and c.TPL_TS = d.TPL_TS and f.TPL_TS = c.TPL_TS and b.CLAIM_ICN_NU = '123456788444'
Current Result which I am getting with this query
OTH_PAYER_ID PAID_DATE GROUP_CODE REASON_CODE ADJUSTMENT_AMOUNT 5501 07/13/2015 CO 11 23.87 5501 07/13/2015 PR 12 3.76 5501 07/13/2015 OT 32 33.45 2032 07/14/2015 CO 12 23.87 2032 07/14/2015 OT 14 43.01
Desired/Expected Result for which I need updated query
i have 2 columns named firstname and lastname, i need to get them into 1 column named name with a space between them. Does anyone have a tip to do this?
Hi,I was trying to retrieve some data in such a way that it 2 columns willbe merged into one, with a column in between. I am trying to dosomething like this:SELECT LastName + ", " + FirstName AS NameFROM EmployeeTBLORDER BY LastNameBut SQL Server does not like this syntax (though it does work with"LastName + FirstName").I appreciate any help.Thanks,Aaron
I'm actually a Mysql user, not a SQL Server user, just becouse the business I work uses it. But the problem I've had I think doesn't have any relation on the plataform it's running...
This is a construction software... I'll try my best to explain you : My table has 4 main columns (Face,Station,Combination,sAs). Face , Station and Combination form a primary key (never repeat together) and the "sAs" represents a calculus between some parameters.
The problem (it became a challenge already :D) consists in selecting the Face,Station and Combination where sAs is maximus, BUT grouping ONLY Face and Station.
For example :
Face Station Combination sAs F1 0 Comb1 45 F1 0 Comb2 13 F1 0 Comb3 30 --- F1 10 Comb1 42 F1 10 Comb2 60 F1 10 Comb3 12 --- F2 0 Comb1 32 F2 0 Comb2 15 F2 0 Comb3 01 --- F2 10 Comb1 02 F2 10 Comb2 07 F2 10 Comb3 23
Here is the challenge : If you execute the following query : "select Face,Station,Combination,max(sas) as sAS from test group by Face,Station" it returns you an arbitrary Combination for the rows in the resultset.
Face Station Combination sAs F1 0 Comb1 45 F1 10 Comb1 60 F2 0 Comb1 32 F2 10 Comb1 23
But the combination I'd like to have is the combination related to the maximus sAs in F1/10, Comb2 instead of Comb1 returned..... The same occurs with the last row F2/10.
The query I wanna find should return the following resultset.
Face Station Combination sAs F1 0 Comb1 45 F1 10 Comb2 60 F2 0 Comb1 32 F2 10 Comb3 23
Please, if somebody has any idea, share it to help me....
Thanks a lot..
Rodrigo
Some code to make it easier to try.... Maybe it has some difference between SQL Server and MySQL.
////////////// CUT HERE
create table test( Face varchar(20), Station int, Combination varchar(20), sAs int );
insert into test values('F1', 0, 'Comb1', 45); insert into test values('F1', 0, 'Comb2', 3); insert into test values('F1', 0, 'Comb3', 30); insert into test values('F1', 10, 'Comb1', 42); insert into test values('F1', 10, 'Comb2', 60); insert into test values('F1', 10, 'Comb3', 12); insert into test values('F2', 0, 'Comb1', 32); insert into test values('F2', 0, 'Comb2', 15); insert into test values('F2', 0, 'Comb3', 01); insert into test values('F2', 10, 'Comb1', 02); insert into test values('F2', 10, 'Comb2', 07); insert into test values('F2', 10, 'Comb3', 23);
select Face,Station,Combination,max(sas) as sAS from test group by Face,Station; ////////////// CUT HERE
Hi,I have the following SQLSELECT Table1.Col1, Table3.Col1 AS Expr1,COUNT(Table1.Col2) AS Col2_No, COUNT(Table1.Col3) AS Col3_No etc,FROM Table3INNER JOIN Table2 ON Table3.Col1=Table2.Col1RIGHT OUTER JOIN Table1 ON Table2.Col2=Table2.Col2GROUP BY Table1.Col1, Table3.Col1The output rows have a value in either Table1.Col1 or Table3.Col1 but notboth.I'd like to combine Table1.Col1 and Table3.Col1 and group by the combinedcolumn in the result but don't know how.Thanks gratefully
I am using sql table named as product which is having columns partno,partnm,weight,surfacearea,totalhr,type
I want sum of weight,surfacearea,totalhr and grouping on partno,partnm,type
If I use query select partno,partnm,sum(weight),sum(surfacearea),sum(totalhr) from product GROUP BY partno,partnm then its working correctly with sum and grouping but if I use query select partno,partnm,sum(weight),sum(surfacearea),sum(totalhr),type from product GROUP BY partno,partnm,type then it is not grouping as expected.
why if third column included in group by clause its not working correctly...Is there any way to group as I want.
Hi, I just read on web that we can not use grouping columns in a variable when using PIVOT operator. For example like, USE AdventureWorks GO SELECT VendorID, [164] AS Emp1, [198] AS Emp2, [223] AS Emp3, [231] AS Emp4, [233] AS Emp5 FROM (SELECT PurchaseOrderID, EmployeeID, VendorID FROM Purchasing.PurchaseOrderHeader) p PIVOT ( COUNT (PurchaseOrderID) FOR EmployeeID IN ( [164], [198], [223], [231], [233] ) // cannot put these in a variable like @Col ) AS pvt ORDER BY VendorID;
Though it can be achieved using when making the query using dynamic sql. If some can make it clear why it is possible using dynamic sql and not with the above code.
I am attempting to get one line with all the price info for three fields. The end result looks like this PT_UD_KeyPatternPrice CastingPrice MachiningPrice FG-020006-000 0.00 1840.00 0.00 FG-020006-000 750.00 0.00 0.00 FG-020006-000 0.00 0.00 3945.00
SELECT PT_UD_KEY, 'PatternPrice' = CASE WHEN PT_UD_NUMBER = 1 THEN TOTAL ELSE 0.00 END, 'CastingPrice' = CASE WHEN PT_UD_NUMBER = 15 THEN TOTAL ELSE 0.00 END, 'MachiningPrice' = CASE WHEN PT_UD_NUMBER = 3 THEN TOTAL ELSE 0.00 END FROM CAL.dbo.EXT00103 WHERE PT_WINDOW_ID='ITEM PRICE' AND LEFT(PT_UD_KEY,2)='FG' AND PT_UD_NUMBER IN (1,3,15) AND TOTAL >0.00 ORDER BY PT_UD_KEY
In the below query, I can get the individual/single group by columns as well as multiple but I cannot control the order in which I would like to group by the data.
So lets say I want to group the data by OS->browser->browser_version(just one example) then I cannot achieve that as the order of OS column comes later in the query.
I know one option would be to write a dynamic SQL but i dont want to do that because of performance reasons. Any other way this can be achieved?
select case when @include_browser = 1 then browser_name end as browser_name, case when @include_browser_version = 1 then browser_version end as browser_version,
Hi,I have data stored as in below sample :-------------------------------+---------------------------------+--------------DateBegin | DateEnd | Rate-------------------------------+---------------------------------+--------------2005-11-13 00:00:002005-11-14 00:00:0063.00002005-11-14 00:00:002005-11-15 00:00:0063.00002005-11-15 00:00:002005-11-16 00:00:0045.00002005-11-16 00:00:002005-11-17 00:00:0045.00002005-11-17 00:00:002005-11-18 00:00:0045.00002005-11-18 00:00:002005-11-19 00:00:0045.00002005-11-19 00:00:002005-11-20 00:00:0045.00002005-11-20 00:00:002005-11-21 00:00:0063.00002005-11-21 00:00:002005-11-22 00:00:0063.0000-------------------------------+---------------------------------+--------------I have to group the select query in this way :-------------------------------+---------------------------------+--------------DateBegin | DateEnd | Rate-------------------------------+---------------------------------+--------------2005-11-13 00:00:002005-11-15 00:00:0063.00002005-11-15 00:00:002005-11-20 00:00:0045.00002005-11-20 00:00:002005-11-22 00:00:0063.0000-------------------------------+---------------------------------+--------------When I run below grouped statement, I get follewed result:SELECT MIN(DateBegin) AS DateBegin, MAX(DateEnd) AS DateEnd,Rate FROM X GROUP BY Rate-------------------------------+---------------------------------+--------------DateBegin | DateEnd | Rate-------------------------------+---------------------------------+--------------2005-11-13 00:00:002005-11-22 00:00:0063.00002005-11-15 00:00:002005-11-20 00:00:0045.0000-------------------------------+---------------------------------+--------------How can I do a query like in 2nd sample from top?best regards,rustam bogubaev
