I am not to experienced with SQL and I guess the following is a piece of cake for most of you...
I want to get the sales amount for each shop, for each of three different products, and for the sum of the three products. The problem is that I only get for each shop for each of the product, but not the sum of the three products OR for each shop the total of the three products, but not for each product.
My query looks like this:
SELECT
Shop_name,
case
when prod in (1,2,3) then ‘milk_bread_butter’
when prod = 1 then ‘milk’
when prod = 2 then ‘bread’
when prod=3 then ‘butter’
else ‘none’
end as product,
sum(sales_amount) as sales
FROM
Dbo.sales
GROUP BY
Shop_name,
case
when prod in (1,2,3) then ‘milk_bread_butter’
when prod = 1 then ‘milk’
when prod = 2 then ‘bread’
when prod=3 then ‘butter’
else ‘none’
end
The problem is that I only get the lin 'milk_bread_butter' per shop. If I switch the order around in the case statement;
case
when prod = 1 then ‘milk’
when prod = 2 then ‘bread’
when prod=3 then ‘butter’
when prod in (1,2,3) then ‘milk_bread_butter’
else ‘none’
I get the sales amounts per shop for each of the products, but not for the milk_bread_butter total.
I would be very greatful for any guidance as how to solve this.
How does one get the primary key of the row that is joined in via agroup by aggregate clause when the aggregate is not performed on theprimary key?For example,Person table(PersonID int,FirstName varchar(50)LastName varchar(50))Visit table(VisitID int,PersonID int,VisitDate datetime)These are simplified versions of my tables. I'm trying to create aview that gets the first time each person Visited:selectp.PersonID,min(v.VisitDate)fromVisit vjoinPerson p on p.PersonID = v.PersonIDgroup byp.PersonIDThe problem is that I would like to return the VisitID in theresultset, but when I do it expands the query since I have to also putit in the group by clause.What are the different ways to achieve this?Subqueries?Only return the date and then join off of date on the outside?Neither of these seem too entising...Thanks in advance for any help.-Dave
I have a SQL statement below which has a case clause which works fine, until I want to group it. Is it possible to group on a clause that is defined as case?
declare @sdate datetime set @sdate = '02/01/2002' select distinct count(tbm.otheridnumber), instatus = case inmate_status when 'inactive' then 'Out of system' else 'Active' end,inm.df_institutionname from tb_medication tbm inner join inmateid inm on tbm.inmate_cntr_id = inm.inmate_cntr_id where tbm.chdpedt is null and DATEDIFF(day,chdpstdt , @sdate) > 90 group by instatus, inm.df_institutionname order by inm.df_institutionname
Select Signal_id, Test = CASE WHEN signal_date > dateadd(hour,-24,getdate()) THEN 'Today' WHEN signal_date > dateadd(hour,-48,getdate()) and signal_date < dateadd(hour,-24,getdate()) THEN 'Today-1' WHEN signal_date > dateadd(hour,-72,getdate()) and signal_date < dateadd(hour,-48,getdate()) THEN 'Today-2' WHEN signal_date > dateadd(hour,-96,getdate()) and signal_date < dateadd(hour,-72,getdate()) THEN 'Today-3' WHEN signal_date > dateadd(hour,-120,getdate()) and signal_date < dateadd(hour,-96,getdate()) THEN 'Today-4' WHEN signal_date > dateadd(hour,-144,getdate()) and signal_date < dateadd(hour,-120,getdate()) THEN 'Today-5' WHEN signal_date > dateadd(hour,-168,getdate()) and signal_date < dateadd(hour,-144,getdate()) THEN 'Today-6' WHEN signal_date > dateadd(hour,-192,getdate()) and signal_date < dateadd(hour,-168,getdate()) THEN 'Today-7' WHEN signal_date > dateadd(hour,-216,getdate()) and signal_date < dateadd(hour,-192,getdate()) THEN 'Today-8' WHEN signal_date > dateadd(hour,-240,getdate()) and signal_date < dateadd(hour,-216,getdate()) THEN 'Today-9' ELSE 'Other' END , Total = count(*) From abmsignal WHERE Signal_id = 'fail test' AND Signal_date > dateadd(hour,-240,getdate()) Group by Signal_id, Test COMPUTE AVG(SUM(Total))
That's what I would like, Daily (or rather 24 hour periods) listed, and then averaged out. I will fix the dateadds so that they are for previous 10 days rather than previous 240 hours, but first I need to get it to work as is.
I have the below query and I am not sure if this will return an accurate aggregate, I know I cannot just group by my alias GLG_DELEGATE_ID, is this the way to handle aggregates when you have a CASE in the SELECT statement?
SELECT CASE WHEN C.GLG_DELEGATE_ID IS null THEN C.GLG_ID ELSE C.GLG_DELEGATE_ID END AS GLG_DELEGATE_ID ,COUNT(P.CONSULTATION_ID) ACTIVITY_AMOUNT FROM dbo.CONSULTATION C GROUP BY C.GLG_DELEGATE_ID , C.GLG_ID
Hi guys, I am having a little diffulty displaying two columns next to each other in a case/group by statement as code shown below. I was wondering if i could have the results displayed such that the gst_amount and total_amount are in two separate columns (as they currently are) however the results of the rows are in the same row not in separate rows (as they are currently). I dont think i can do this in the current case statement that i have (i.e: the two case statements). Any feedback would be appreciated
SELECT distinct PERIOD.STARTDATE, temp_111.EVENTTYPEID, case when temp_111.[name] like '%GST%' then sum(temp_111.CONTRIBUTIONVALUE) end as GST_AMOUNT, case when temp_111.[name] not like '%GST%' then sum(temp_111.CONTRIBUTIONVALUE) end as Total_Amount FROM temp_111 INNER JOIN PERIOD ON temp_111.PERIODSEQ = PERIOD.PERIODSEQ WHERE (NOT temp_111.PRODUCTID = 'IIIE' OR temp_111.PRODUCTID IS NULL) AND temp_111.PERIODSEQ in ('111') group by PERIOD.STARTDATE, temp_111.EVENTTYPEID, temp_111.[name]
Hi,Can anyone please help me with SQL syntax to create a second variablebased on the value of another (both numeric)?My effort is below but I get a syntax error.SELECTcharA,CASE charAWHEN < -199 THEN 2WHEN < 31 THEN 3WHEN < 82 THEN 4WHEN < 100 THEN 5WHEN < 105 THEN 6WHEN < 111 THEN 7WHEN < 143 THEN 8WHEN < 165 THEN 9WHEN < 233 THEN 10WHEN >= 233 THEN 11END AS Bin_charAFROM XServer: Msg 170, Level 15, State 1, Line 6Line 6: Incorrect syntax near '<'.
It appears that table grouping is case sensitive (for example, Re-Roof versus Re-roof appears to be causing a group break). I can't find a parameter to change this behaviour in Reporting Services. Can anyone verify that it is in fact case sensitive? How to change?
I am running SQL Server 2000 and the database that I am querying is not case sensitive.
I've always been mistified why you can't use a column alias in the group byclause (i.e. you have to re-iterate the entire expression in the group byclause after having already done it once in the select statement). I'mmostly a SQL hobbiest, so it's possible that I am not doing this in the mostefficient manner. Anyone care to comment on this with relation to thefollowing example (is there a way to acheive this without re-stating theentire CASE statement again in the Group By clause?):Select 'Age' =CaseWHEN(SubmittedOn >= DATEADD(dd, - 30, GETDATE())) AND (SubmittedOn <=DATEADD(dd, - 0, GETDATE()))THEN '0-30 Days Old'WHEN(SubmittedOn >= DATEADD(dd, - 60, GETDATE())) AND (SubmittedOn <=DATEADD(dd, - 31, GETDATE())) Then '31-60 Days Old'WHEN(SubmittedOn >= DATEADD(dd, - 90, GETDATE())) AND (SubmittedOn <=DATEADD(dd, - 61, GETDATE())) Then '61-90 Days Old'WHEN(SubmittedOn <= DATEADD(dd, - 91, GETDATE())) THEN '91+ Days Old'ELSE cast(SubmittedOn as varchar(22))end,max(SubmittedOn), COUNT(SCRID) AS NbrSCRsFrom SCRViewWHERE(StatusSort < 90) ANDCustomerID = 8 andUserID = 133group byCaseWHEN(SubmittedOn >= DATEADD(dd, - 30, GETDATE())) AND (SubmittedOn <=DATEADD(dd, - 0, GETDATE()))THEN '0-30 Days Old'WHEN(SubmittedOn >= DATEADD(dd, - 60, GETDATE())) AND (SubmittedOn <=DATEADD(dd, - 31, GETDATE())) Then '31-60 Days Old'WHEN(SubmittedOn >= DATEADD(dd, - 90, GETDATE())) AND (SubmittedOn <=DATEADD(dd, - 61, GETDATE())) Then '61-90 Days Old'WHEN(SubmittedOn <= DATEADD(dd, - 91, GETDATE())) THEN '91+ Days Old'ELSE cast(SubmittedOn as varchar(22))endOrder by max(submittedon) descThanks,Chad
When I created a CASE statement (This is at work, Pat:)) it is about 30-40 lines long. I gave it a name and set the name = to the case statement:
ie,
SELECT fieldname1 = CASE WHEN condition THEN 'blah blah' WHEN condition THEN 'blah blah' WHEN condition THEN 'blah blah' ELSE thisandthat END , fieldname2 , fieldname3 FROM tablename1 GROUP BY CASE WHEN condition THEN 'blah blah' WHEN condition THEN 'blah blah' WHEN condition THEN 'blah blah' ELSE thisandthat END, , fieldname2, fieldname3
etc.
The long CASE statement in my GROUP BY is awkward to me. Is this the only way to do it? I tried using the fieldname1 but it comes back as an invalid field name and asks for the "expression".
I am using SQL Server 2005 and fairly new at using SQL Server. I am having problems using a Case statements in the select list while have a group by line. The SQL will parse successfully but when I try to execute the statement I get the following error twice :
Column 'dbo.REDEMPTIONHISTORY.QUANTITY' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
Below is the my sql statement:
SELECT dbo.DateOnly(TH.TransactionDate) AS RptDate, RH.Item,
ItemRef =
Case
when RH.Quantity < 0 then Sum(RH.Quantity)
when RH.Quantity >= 0 then Sum(0)
end
FROM dbo.RHISTORY AS RH INNER JOIN dbo.TRANSHISTORY AS TH ON RH.TRANSACTIONID = TH.TransactionID
WHERE (dbo.DateOnly(TH.TransactionDate) BETWEEN '10-1-2007' AND '10-5-2007') AND (RH.TransactionCode IN (13, 14, 15, 16))
Group by dbo.DateOnly(TH.TransactionDate), RH.Item
The TransHistory table contains, primary key transactionid, TransactionDate and the RHistory contains all the details about the transaction, the RHistory table is joined to the TransHistory table by foreign key TransactionID. I am trying to get totals for same item on the same day.
Any help will be greatly appreciated. I am also having trouble using If..Then statements in a select list and can not fin the correct syntax to use for that.
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
I have an SSRS 2012 table report with groups; each group is broken ie. one group for one page, and there are multiple groups in multiple pages.
'GroupName' column has multiple values - X,Y,Z,......
I need to group 'GroupName' with X,Y,Z,..... ie value X in page 1,value Y in page 2, value Z in page 3...
Now, I need to display another column (ABC) in this table report (outside the group column 'GroupName'); this outside column itself is another column header (not a group header) in the table (report) and it derives its name partly from the 'GroupName' Â values:
Example:
Value X for GroupName in page 1 will mean, in page 1, column Name of ABC column must be ABC-X Value Y for GroupName in page 2 will mean, in page 2, column Name of ABC column must be ABC-Y Value Z for GroupName in page 3 will mean, in page 3, column Name of ABC column must be ABC-Z
ie the column name of ABC (Clm ABC) Â must be dynamic as per the GroupName values (X,Y,Z....)
Page1:
GroupName          Clm ABC-X
X
Page2:
GroupName          Clm ABC-Y
Y
Page3:
GroupName          Clm ABC-Z
Z
I have been able to use First(ReportItems!GroupName.Value) in the Page Header to get GroupNames displayed in each page; I get X in page 1, Y in page 2, Z in page 3.....
However, when I use ReportItems (that refers to a group name) in the Report Body outside the group,
I get the following error:
Report item expressions can only refer to other report items within the same grouping scope or a containing grouping scope
I need to get the X, Y, Z ... in each page for the column ABC.
I have been able to use this - First(Fields!GroupName.Value); however, I get ABC-X, ABC-X, ABC-X in each of the pages for the ABC column, instead of ABC-X in page 1, ABC-Y in page 2, ABC-Z in page 3, ...
I have created an SQL server table in the past on a server that was all case sensative. Over time I found out that switching to a server that is not case sensative still caused my data to become case sensative. I read an article that said you should rebuild your master database then re-create your tables. So after rebuilding the master database, a basic restore would not be sufficient? I would have to go and manually re-create every single table again?
Can someone point me to a tutorial on how to search against a SQL Server 2000 using a case insensitive search when SQL Server 2000 is a case sensitive installation?
We need to install CI database on CS server, and there are some issueswith stored procedures.Database works and have CI collation (Polish_CI_AS). Server hascoresponding CS collation (Polish_CS_AS). Most queries and proceduresworks but some does not :-(We have table Customer which contains field CustomerID.Query "SELECT CUSTOMERID FROM CUSTOMER" works OK regardless ofcharacter case (we have table Customer not CUSTOMER)Following TSQL generate error message that must declare variable @id(in lowercase)DECLARE @ID INT (here @ID in uppercase)SELECT @id=CustomerID FROM Customer WHERE .... (here @id in lowercase)I know @ID is not equal to @id in CS, but database is CI and tablenames Customer and CUSTOMER both works. This does not work forvariables.I suppose it is tempdb collation problem (CS like a server collationis). I tried a property "Identifier Case Sensitivity" for myconnection, but it is read only and have value 8 (Mixed) by default -this is OK I think.DO I MISS SOMETHING ????
I am working in a SQL server database that is configured to be case-insensetive but I would like to override that for a specific query. How can I make my query case-sensitive with respect to comparison operations?
I am curious with using replication in sql server 2005 one way from db A (source) replicating to db B(destination) in which db A has a collation of CS and db B has a collation of CI. Will there be any problems with this scenario? Thanks in advance!
I have a view where I'm using a series of conditions within a CASE statement to determine a numeric shipment status for a given row. In addition, I need to bring back the corresponding status text for that shipment status code.
Previously, I had been duplicating the CASE logic for both columns, like so:
Code Block...beginning of SQL view... shipment_status = CASE [logic for condition 1] THEN 1 WHEN [logic for condition 2] THEN 2 WHEN [logic for condition 3] THEN 3 WHEN [logic for condition 4] THEN 4 ELSE 0 END, shipment_status_text = CASE [logic for condition 1] THEN 'Condition 1 text' WHEN [logic for condition 2] THEN 'Condition 2 text' WHEN [logic for condition 3] THEN 'Condition 3 text' WHEN [logic for condition 4] THEN 'Condition 4 text' ELSE 'Error' END, ...remainder of SQL view...
This works, but the logic for each of the case conditions is rather long. I'd like to move away from this for easier code management, plus I imagine that this isn't the best performance-wise.
This is what I'd like to do:
Code Block ...beginning of SQL view... shipment_status = CASE [logic for condition 1] THEN 1 WHEN [logic for condition 2] THEN 2 WHEN [logic for condition 3] THEN 3 WHEN [logic for condition 4] THEN 4 ELSE 0 END,
shipment_status_text =
CASE shipment_status
WHEN 1 THEN 'Condition 1 text'
WHEN 2 THEN 'Condition 2 text'
WHEN 3 THEN 'Condition 3 text'
WHEN 4 THEN 'Condition 4 text'
ELSE 'Error'
END, ...remainder of SQL view...
This runs as a query, however all of the rows now should "Error" as the value for shipment_status_text.
Is what I'm trying to do even currently possible in T-SQL? If not, do you have any other suggestions for how I can accomplish the same result?
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.
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.
I have an SSRS report with groups that when exported to excel contains drill-in's (plus marks on left side). The issue I have is that for all the groups in the drill-in, those cells become merged. I want to keep the group drill-in but have the cells UNMERGED. I have heard this can be done with the RDL XML but I don't know what to modify to accomplish this.Â
I am working on a C#/asp.net web application. The application has a text box that allows a user to enter a name. The name is then saved to the database. Before the name is saved to the database, I need to be able to check if the name already exists in the database. The problem here is that what if the name is in the database as "JoE ScMedLap" and somoene enters the name as "Joe Schmedlap" which already exists in the database,but just differs in case. In other words how do deal with case sensitiviy issues.
Yesterday I received a response to my CI/CS Collation problem and therecommendation was to try and restore a CI Collation database to a CSCollation database. After creating a blank CS database a full restore(Force restore over existing database) does change the Collation toCI. I'm unsure as to how I can restore without changing theCollation. Any suggestions?
I'm having a fight with Reporting Services at the minute when trying to compute an average at the row group level for a value summed in a column group.I have the following column groups:
Year Month Date
And the following row groups:
Region Product SubType (hidden, data at the date level is summed to Product)
At the moment I'm computing the average for SubType for each Date at the Product level (giving a decimal value), so for each day I end up with a nice average, that works. However I am unable to average that average over the whole Year for a Product. The issue being that I'm trying to combine Row Groups (Product) and Column Groups (Date/Year)
select top 15 count(*) as cnt, state from table group by state order by cnt desc
[code[...
Can the above three queries be combined into one and still be fast, if so how?What i am trying to go is an item count, by group, similar to ones Inbox in Outlook.
I have a need to show a row inside a table group to simulate a header row for the data rows inside the group. The table will not have a real header or footer. Thanks for the help.
I have one domoain in the forest. The domain level is set to Windows 2000 native mode and forest level is set to mixed mode. My SQL server 2005 server joined to this domain. I added a brand new domain local group and add a normal user account to this domain local group. I login to the SQL server 2005 server and make a query "SELECT * FROM sys.login_token". I cannot see my domain local group in sys.login_token. However, if I add my account to a global group, I can see it there.
Then, I setup another forest. This time, I have domain level set to Windows 2003 mode and forest level is set to Windows 2003 native mode. I do the same testing. This time, I can see my domain local group in sys.login_token.
Why does SQL server 2005 has this limitation? Is it a bug?
I'm banging my head against the wall. It's probably pretty simple so here goes...
I have a form that supposed to add records to a table on the database. I'm pretty sure I have the dataAdapter and Dataset set right but when I add a record, it complains that the primary key can’t be null. I’ve tried different things, including removing those fields and variables from the stored Proc, changing the Identity Properties of the Table, changing the AutoIncrement settings on the Dataset for that field, but all give me different errors. Nothing actually makes it work. This database has many records in it and the Primary key is a decimal field that starts at 5, then next was 10, then 28, then it started getting consecutive after 110. (111, 112, 113, etc.) (I'm porting over an old database to a new web site, so I'm somewhat contrained by the database.)
I guess my question is for adding records in to a database that has a primary unique key constraint. Certainly this is a pretty common thing. Obviously I do not want users to enter that in themselves, I thought it should simply autocreate/autonumber. So what is the best procedure and gotcha’s I need to watch out for?
if i have the following code, how do i know if the transaction was actually successful, so basically where in this code can i write TRANSACTION SUCCESFUL?
try { //Open up the connection conn.Open(); //Setup the Transaction trans = conn.BeginTransaction();
//First query in transaction pre_query = "Delete from Menu where spec_name=" + "'" + spec_name_array[i].Text.ToString() + "'" ;
//Second query in transaction query = "INSERT into Menu VALUES ('" + Calendar1.SelectedDate +"','" + spec_name_array[i].Text.ToString() +"','" + spec_desc_array[i].Text.ToString() +"','" +spec_price_array[i].Text.ToString() +"',1)"; SqlCommand comm = new SqlCommand(pre_query,conn); //Setup the command to handle transaction comm.Transaction = trans; //Execute first query comm.ExecuteNonQuery(); //Add in second query comm.CommandText = query; //Execute second query comm.ExecuteNonQuery(); trans.Commit(); } catch(SqlException ex) { com_label.Text = "Submission Not complete, did you forget the date or something else?"; //Undo all queries trans.Rollback(); } finally { conn.Close(); }
