Required output: parameters are Subject column values
ClassId ClassName TotalStudents SubjectIds
1 ESL 1 20, 40, 50
1 ESL 1 30
When one student takes multiple subjects then count student only once, so in the above data 12 is one student id takes multiple subjects so counted only once. TotalStudents value is 1
I did write below query:
Declare @subjectids string
set @subjectids = '20,30,40,50'
-- will split @subjectids and store in temp table
select classname, classid, Count(Distinct StudentId)
from mytable
where SubjectsIds in @subjectIds
group by ClassId, ClassName, SubjectId,
I need write a query for removing duplicates, for Example in my table I have columns
A_ID name id 1 sam 10 2 sam 10 3 sam 10 4 sam 10 5 ccc 15 6 ccc 15 7 ccc 15 8 fff 20 9 fff 20 10 fff 20
So now I have duplicates values in id column so now I need to take only one value of each and delete the remaining. I need to take first id value 10,15,20 so only 3 rows should be there in my table.
I am trying to count a column field in a single table and return two count values as one record set using group by.
field1 = group by (department) nvarachar field2 = count (closed) datetime
I have tried using derived tables with no luck getting the desired result.
field2 is a datetime field as indicated I want a count for two conditions
1. WHERE field2 is null 2. WHERE field2 is not null
End Results would like this ====== Department | OpenItems | ClosedItems Department1 | 32 | 24 Departmnet2 | 87 | 46 Department3 | 42 | 76
=======
I got it *almost* working with derived tables, but the group by function was not putting the department as one single row. I was getting multiple rows for departments.
I realize this is probably a simple answer and I am making this a lot harder than it actually is....
We have SharePoint list which has, say, two columns. Column A and Column B.
Column A can have three values - red, blue & green.
Column B can have four values - pen, marker, pencil & highlighter.
A typical view of list can be:
Column A - Column B red - pen red - pencil red - highlighter blue - marker blue - pencil green - pen green - highlighter red - pen blue - pencil blue - highlighter blue - pencil
We are looking to create a report from SharePoint List using SSRS which has following view:
red blue green pen 2 0 1 marker 0 1 0 pencil 1 3 0 highlighter 1 1 1
We tried Sum but not able to display in single row.
How can I display 0 when using COUNT and GROUP BY?I'm using SELECT CASE in my query. I was trying to use COALESCE but no result, COUNT result = 1. (there should be 0).
COALESCE((COUNT(DISTINCT (CAST((CASE WHEN CurrStat = @Stat AND LogDate = @LogDate THEN Enumber ELSE 0 END) AS int)))), 0) AS InTrack,
I am trying to find a count on group of our memberid`s who were active within a year since 2010 till today for particular memberships in my table I have memberid int effectivedate datetime termdate datetime Membershiptype varchar(10) ='GOLD','Silver' and 'Platinum'. I haven't used sql in a long time..
I am using SQL 2012. I have a GROUP BY and I want to select two other fields from my table at the same time: One column that is a string (account_code) and one that I need to perform a count on (customer_number). I know the code COUNT(DISTINCT customer_number) works for getting that. I need to select both of those fields on top of what I have. I have the following:
DECLARE @Providers TABLE (ID INT IDENTITY(1,1), Provider_Name VARCHAR(20), Uniq_Id VARCHAR(10), Total_Spent MONEY, Total_Earned MONEY) INSERT INTO @Providers (Provider_Name, Uniq_Id,Total_Spent, Total_Earned)
Hello Everyone I've created a report with a simple dataset that is similar to this
City , RequestID, Request Amount, ClaimID, ClaimAmount El Monte 791 52,982.00 2157 41,143.75 El Monte 3691 11,838.00 3140 8,231.14 El Monte 3691 11,838.00 3141 990.00 El Monte 3691 11,838.00 3142 2,615.00
So I group by City, RequestID. On the first group I specified the expression to be City and in the header I list the city and in the footer I list the sum of Request amount. On the second group I specified the group by Request so in the header I placed requestID and on the footer I placed Request Amount. I set request information to hide the duplicates and I even add =Sum(Fields!RequestApprovedGrandTotal.Value,"GroupByRequestID") the scope of the group. But this is what I get:
For requestID = 3691 for Request Amount is 35,514.00 not 11,838.00. All the claim sums are correct and they are located on the detail row.
I've read that a work around is to create multiple dataset but I honestly believe that something as simple as this should work on the reporting server 2000. So I've come to the conclusion that I must be doing something wrong. Can someone give me a hand on this. Thanks.
I am attempting to create a simple recordset that would return thenumber of duplicates that exist in a table with a single column. Forexample if I had a table like the following:ID Reference Amount1 123456 1.002 123456 2.003 123 1.00I would like to get the following result:ID Reference Amount RecCount1 123456 1.00 22 123456 2.00 23 123 1.00 1Please help!Thanks,Shawn
I have a table of users and date when they logged on to a system. I am trying to count how many distinct users logged on for each day of the week. The SQL below works when there's at least a user for each day. But when there is no user for a particular day such as Sunday, I still want it to return "SUN
0 "
I learned that you can use GROUP BY ALL and it works but the "ALL" is deprecated beyond SQL 2005.
------------------------------------ SELECT UPPER(LEFT(DATENAME(dw, StartTime), 3)) AS DayOfWeek, COUNT(DISTINCT UserID) AS NumberOfUser
FROM testUserLoginDuration WHERE Archived = 0 GROUP BY UPPER(LEFT(DATENAME(dw, StartTime), 3)) ORDER BY CASE WHEN UPPER(LEFT(DATENAME(dw, StartTime), 3)) = 'MON' THEN 1 WHEN UPPER(LEFT(DATENAME(dw, StartTime), 3)) = 'TUE' THEN 2 WHEN UPPER(LEFT(DATENAME(dw, StartTime), 3)) = 'WED' THEN 3 WHEN UPPER(LEFT(DATENAME(dw, StartTime), 3)) = 'THU' THEN 4 WHEN UPPER(LEFT(DATENAME(dw, StartTime), 3)) = 'FRI' THEN 5 WHEN UPPER(LEFT(DATENAME(dw, StartTime), 3)) = 'SAT' THEN 6 WHEN UPPER(LEFT(DATENAME(dw, StartTime), 3)) = 'SUN' THEN 7 END
We have a table with 1 million rows with duplicates in a column which allows nulls.Can we enforce uniqueness for only future inserts by anyway(ignoring the old ones)? Thanks!
Is there a setting in SQL Server that ensures a column is not allowed to have the same value more than once? Or must this be set up in the insert statment itself? Or how about a business rule?
I have a stored procedure that returns a single row based on a parameter of employee ID. This particular procedure uses a CTE to traverse our org structure. One of the columns is returning a delimited string of Windows login values and due to the business rules, it can contain duplicate values. I need to have that column contain only unique values - no dupes.
For example, this one column could contain something like this:
With the data example below I am trying to consolidate the duplicate rows by flattening the dealer and billcode, or putting those values in each of the columns instead of creating separate rows...
74 MARTHA PATNE RIPLEY 1 23,327,76 ROTTINGDAM AAC SPRINGFIELD 3052 USA MPATRIP@AMERICANALARM.COM,MPATRIP@COMCAST.NET,PRIPLEY@ONECOMMUNICATIONS.COM
I have a requirement where i want to delete the records based on the Date column. I have table which contain the columns like machinename ,lasthardwarescandate
I want to delete the records based on the max(Lasthardwarescandate) i.e. latest one, column where the machine name is duplicate menace it repeats. So how would i remove the duplicate machine names based on the Lasthardwarescandate column(There are multiple entries for the Lasthardwarescandate so i want to fetch the latest date column).
Note: Duplication should be removed based on “Last Hardware Scan” date.
Only latest date should be considered from multiple records for the same system. "
i dont't know how to select row with max column value group by another column. I have T-SQL
CREATE PROC GET_USER AS BEGIN SELECT T.USER_ID ,MAX(T.START_DATE) AS [Max First Start Date] , MAX(T.[Second Start Date]) AS [Max Second Start Date], T.PC_GRADE,T.FULL_NAME,T.COST_CENTER,T.TYPE_PERSON_NAME,T.TRANSACTION_NAME,T.DEPARTMENT_NAME ,T.BU_NAME,T.BRANCH_NAME,T.POSITION_NAME FROM (
Basically it's converting certain row values to new column. Every PloicyNum will have 1001 to 1006 Fixed InsCode values as a group.
Rule-1: InsCode value 1001 should always mapped to 1005 InsCode value 1002 should always mapped to 1006 InsCode value 1003 should always mapped to 1004
Rule-2: For a policyNum, If any Inscode value is missed from the group values 1001 to 1006, still need to mapped with corresponding values as shown in Rule-1
In the above sample data..
for PolicyNum - 1ABC20 , group values 1003,1006 are missing for PolicyNum - 1ABC25 , group values 1002,1003,1004,1005,1006 are missing
Create Table sampleDate (PolicyNum varchar(10) not null, InsCode Varchar(4) not null) Insert into Sample Date(PolicyNum, InsCode) Values ('1ABC12','1001')
Insert into Sample Date(PolicyNum, InsCode) Values ('1ABC12','1002') Insert into Sample Date(PolicyNum, InsCode) Values ('1ABC12','1003')
I have 2 excel tables files.One table has info about sales by country, by model, by date...and the other table has units by date.Obviously, the common key is "date"....and by creating a relationship, I can add "units" to my combined resulting table.However, I can't create the relationship. Excel keeps telling me: "the relationship cannot be created because each column contains duplicate values. Select at least one column that contains only unique values".
A: Id Reason Amount Subject RecordNo 1 Gift 100 first 11 2 Gift Reason 200 second 12 3 Gift Reason 100 first 11
The result that I want is :
Reason Amount Subject ALL 200 first i used the following query
select case when Grouping(B.Subject) <> '' then 'ALL' else B.Subject End as Subject , case when Grouping(B.reason) <> '' then 'ALL' else B.Reason END as Reason, SUM(B.amount) as amount from B where B.RecordNo = 11 group by grouping sets (B.Reason,B.Subject)
but it's giving me 3 rows in return.
I want only one summation row. How can i get that?
Hello Friends, I am creating a report in which I want to create group column using two field value. Is it possible to do so? We have a requirement in which we are fetching data from two different hierarchy.
A B C D E F G H I J K L 1 2 3....................4 5 M ............................. N ......................... O ....................... P
The report matrix look like the above one. The elements A,B,C are coming from one hierarchy and D,E,F,G,H,I,J,K are coming from other hierarchy. But i have created one data set to fetch the values for the report. But while creating the column group I am getting both two diff fields so I am not able to use it in single Column group and I want to use only one column group.
select CurrencyCode,TransactionCode,TransactionAmount,COUNT(TransactionCode) as [No. Of Trans] from TransactionDetails where CAST(CurrentTime as date)=CAST(GETDATE()as date) group by TransactionCode, CurrencyCode,TransactionAmount order by CurrencyCode
select CurrencyCode,TransactionCode,TransactionAmount,COUNT(TransactionCode) as [No. Of Trans] from TransactionDetails where CAST(CurrentTime as date)=CAST(GETDATE()as date) group by TransactionCode order by CurrencyCode
But of course this codes gives an error, but how can I get my desired result??
The following stored procedure works well as far as it goes but I need to amend it.
@AE decimal (18,2) outputAS BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- Insert statements for procedure here
declare@odds decimal (18,2) (SELECT @odds= sum (1/ (RacingMaster.RM_SP) ) FROM RacingMaster where RM_Draw='1')
[Code] .....
The column RM_Draw holds integer values from 1 through to 20. provide a way so that when the procedure has produced a result based on RM_Draw='1'' it will produce a separate result for RM_Draw=2 and so all the way to 20 so that the resultant figures can be outputted to my vb.net app.
When I give studentId 10 and class ids = 100, 101, 102, 103 then result should be get row from student table only if all given class ids matched.
Result: Id Student ID ClassId 1 10 100, 101, 102, 103
Case 2: Student Id: 10 class Ids = 100, 101 the no results since all the class ids for student 10 in Student_Class are not matching with the given class Ids parameter.
I've a table that has salescode(124!080) and salesamount(125.65!19.25) and I need to split the columns. Salesman(124) has commission(125.65). Here is the DDL:
USE tempdb; GO DECLARE @TEST_DATA TABLE ( DT_ID INT IDENTITY(1,1) NOT NULL PRIMARY KEY CLUSTERED , InvNoVARCHAR(10) NOT NULL , SalesCode NCHAR(80) NOT NULL
I have one column called ORGNAME NVarchar(34). There are two records which has the same value stored in that column"Mobile Payment Finland", in both records there are no spaces at start or end position, which mean both records are exactly same. But when i run
SELECT LEN(ORGNAME ) from table
it gives, 34 for first record and 22 for the second record. I couldnt understand what could be the exact problem.
If i use SELECT DATALENGTH(ORGNAME ) from table then i get 68 and 44
Table A(Value), which contains some strings in the column "Value"
Table B(Key,Text), which also contains strings (in the column "Text")
Now I want to find all rows in B which contain at least one string of A and create a result table X with all found rows in B. B should contain the found keys and all found substrings for this key (separated with a comma)
The solution I am looking for may not use a Cursor and may not use the CONTAIN-Statement (fulltextsearch feature)..
