question

sangeetha avatar image
sangeetha asked

Count of a column value group by another column value - need SQL query urgently

Hi, I have a table which has below kind of information: NMI Suffix StartDate EndDate Meter NMI1 E1 2010-06-12 2011-06-29 111111 NMI2 E1 2010-06-12 2011-06-29 111111 NMI2 E2 2010-06-12 2011-06-29 111111 NMI3 K1 2010-06-12 2011-06-29 111111 NMI4 E1 2010-06-12 2011-06-29 111111 Now i have to retrieve the NMI which has more than one E suffix. In the above example my query should return NMI2. Can please advise how to write a query? thank you
sql-server-2008
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

·
Pavel Pawlowski avatar image
Pavel Pawlowski answered
Here you go.. DECLARE @tbl TABLE ( NMI varchar(100), Suffix char(2), StartDate datetime, EndDate datetime, Meter varchar(10) ) INSERT INTO @tbl(NMI, Suffix, StartDate, EndDate, Meter) SELECT 'NMI1','E1','2010-06-12','2011-06-29','111111' UNION ALL SELECT 'NMI2','E1','2010-06-12','2011-06-29','111111' UNION ALL SELECT 'NMI2','E2','2010-06-12','2011-06-29','111111' UNION ALL SELECT 'NMI3','K1','2010-06-12','2011-06-29','111111' UNION ALL SELECT 'NMI4','E1','2010-06-12','2011-06-29','111111' SELECT NMI, COUNT(DISTINCT Suffix) DistinctSuffixes FROM @tbl WHERE LEFT(Suffix, 1) = 'E' GROUP BY NMI HAVING COUNT(DISTINCT Suffix) > 1 --OR eventually in case even repetititon of the same suffix should be took in mind SELECT NMI, COUNT(DISTINCT Suffix) DistinctSuffixes FROM @tbl WHERE LEFT(Suffix, 1) = 'E' GROUP BY NMI HAVING COUNT(LEFT(Suffix, 1)) > 1
3 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

sangeetha avatar image sangeetha commented ·
Thanks much for the response... I have to use these conditions in update query, however it is giving inncorrect syntax at group by error:: CREATE TABLE #NewRequests ( SegmentID INT NOT NULL PRIMARY KEY CLUSTERED ,NMIid int NULL ,NMI varchar (10) not null ,CisAcctNo varchar(50) not NULL ,ServicePrvDate smalldatetime null ,LastBillEndDate smalldatetime null ,Filename varchar(256) not null ,File_Date smalldatetime null , Status char(1) NULL , Error int null ) INSERT INTO #NewRequests ( SegmentID ,NMI ,CisAcctNo ,ServicePrvDate ,LastBillEndDate ,Filename ,File_Date ,Status ) SELECT SegmentID , NMI , CIS_ACCOUNT_NO , SERV_PROV_DATE , LAST_BILL_END_DATE ,FILENAME ,FILE_DATE ,STATUS FROM dbo.tbSmartWorld_TestBed_Requests S WHERE Status IN ( 'N' ) order by SegmentID Update z set Error= 2 From #NewRequests z Inner join vwIntervalMeterConfig v on v.nmiid = z.nmiid and z.FILE_DATE between v.fromdate and v.todate where LEFT(v.suffix,1) = 'E' and z.error is null group by v.NMIid having COUNT(distinct suffix) >1 Can you help how to use it directly in update query? thank you
0 Likes 0 ·
sangeetha avatar image sangeetha commented ·
need it in update query.. please see my comments
0 Likes 0 ·
Pavel Pawlowski avatar image Pavel Pawlowski commented ·
You can put it as IN in wehere condition of the update query. UPDATE Z SET Error = 2 FROM #NewRequests z INNER JOIN vwIntervalMeterConfig v on v.nmiid =znmiid and z.FILE_DATE between v.fromdate and v.todate WHERE z.NMI IN (SELECT NMI FROM #NewRequests WHERE LEFT(v.suffix,1) = 'E' and z.error is null GROUP BY NMI HAVING COUNT(distinct suffix) > 1)
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.