Select Query with grouping - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Select Query with grouping

Author

Topic

anish20in
Starting Member

18 Posts

Posted - 2011-08-05 : 06:47:59

Hi All,
I have the following tables
CREATE TABLE #TEMP1(MID INT,MNO VARCHAR(50),MDATE DATETIME)

CREATE TABLE #TEMP2(STID INT,STNAME VARCHAR(50),MID INT)

INSERT INTO #TEMP1 VALUES(1,'PI0805201101','08/02/2011')
INSERT INTO #TEMP1 VALUES(2,'PI0805201102','08/03/2011')
INSERT INTO #TEMP1 VALUES(3,'PI0805201103','08/04/2011')
INSERT INTO #TEMP1 VALUES(4,'PI0805201104','08/04/2011')

INSERT INTO #TEMP2 VALUES(1,'NAME1',1)
INSERT INTO #TEMP2 VALUES(2,'NAME2',1)
INSERT INTO #TEMP2 VALUES(3,'NAME3',1)
INSERT INTO #TEMP2 VALUES(4,'NAME4',1)
INSERT INTO #TEMP2 VALUES(5,'NAME5',1)
INSERT INTO #TEMP2 VALUES(1,'FNAME1',2)
INSERT INTO #TEMP2 VALUES(2,'FNAME2',2)
INSERT INTO #TEMP2 VALUES(3,'FNAME3',2)
INSERT INTO #TEMP2 VALUES(1,'ENAME1',3)
INSERT INTO #TEMP2 VALUES(2,'ENAME2',3)
INSERT INTO #TEMP2 VALUES(3,'ENAME3',3)
INSERT INTO #TEMP2 VALUES(4,'ENAME4',3)
INSERT INTO #TEMP2 VALUES(1,'LNAME1',4)
INSERT INTO #TEMP2 VALUES(2,'LNAME2',4)

I need the following result

MDATE

STIDCOUNT

MIDCOUNT

08/02/2011

08/03/2011

08/04/2011

STIDCOUNT is the count of STID and MIDCOUNT is the count of MID in a Day.If we have multiple MID's Then also we have to group(the last column).
Please help me for writing this.Also all your suggestion is important to me for this.

Kunjappy

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-08-05 : 07:06:44

For your test data, this should work:

SELECT
   mdate,
   SUM(scount) STIDCOUNT,
   COUNT(*) MIDCOUNT
FROM
   #temp1 t1
   CROSS APPLY
   (
      SELECT COUNT(*) scount
      FROM #temp2 t2
      WHERE t2.mid = t1.mid
   ) t2
GROUP BY
   mdate;

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2011-08-05 : 07:18:36

I may not be understanding the reqs, but this gives the desired output

select t1.mdate,count(t2.stid) as stid,count(distinct t2.mid) as mid
from #temp1 t1
inner join #temp2 t2
on t1.mid = t2.mid
group by t1.mdate

Jim

Everyday I learn something that somebody else already knew

anish20in
Starting Member

18 Posts

Posted - 2011-08-08 : 07:10:17

Thanks Friends.The Queries works for me.
Now i have to modify the results.So I add more columns to the first table.Please see below

CREATE TABLE #TEMP1(MID INT,MNO VARCHAR(50),MDATE DATETIME,MSTOP INT,MMILES VARCHAR(50),MHRS VARCHAR(50),
OSTART INT,OEND INT,DAAM DATETIME,DDPM DATETIME,SH BIT)

CREATE TABLE #TEMP2(STID INT,STNAME VARCHAR(50),MID INT)

INSERT INTO #TEMP1 VALUES(1,'PI0805201101','08/02/2011',10,'77056','42:16',1046,2101,'8/02/2010 06:00:00 AM','8/02/2011 7:15:00 PM',1)
INSERT INTO #TEMP1 VALUES(2,'PI0805201102','08/03/2011',5,'46056','24:19',186,251,'8/03/2010 06:30:29 AM','8/03/2011 7:00:00 PM',1)
INSERT INTO #TEMP1 VALUES(3,'PI0805201103','08/04/2011',6,'20466','12:11',10465,21016,'8/04/2010 08:00:00 AM','8/04/2011 2:30:00 PM',1)
INSERT INTO #TEMP1 VALUES(4,'PI0805201104','08/04/2011',2,'104656','08:00',6046,9101,'8/04/2010 07:30:00 AM','8/04/2011 3:30:00 PM',1)

INSERT INTO #TEMP1 VALUES(5,'PI0805201105','08/03/2011',2,'656','07:00',100,800,'8/03/2010 07:30:00 AM','8/03/2011 3:30:00 PM',0)

INSERT INTO #TEMP2 VALUES(1,'NAME1',1)
INSERT INTO #TEMP2 VALUES(2,'NAME2',1)
INSERT INTO #TEMP2 VALUES(3,'NAME3',1)
INSERT INTO #TEMP2 VALUES(4,'NAME4',1)
INSERT INTO #TEMP2 VALUES(5,'NAME5',1)
INSERT INTO #TEMP2 VALUES(1,'FNAME1',2)
INSERT INTO #TEMP2 VALUES(2,'FNAME2',2)
INSERT INTO #TEMP2 VALUES(3,'FNAME3',2)
INSERT INTO #TEMP2 VALUES(1,'ENAME1',3)
INSERT INTO #TEMP2 VALUES(2,'ENAME2',3)
INSERT INTO #TEMP2 VALUES(3,'ENAME3',3)
INSERT INTO #TEMP2 VALUES(4,'ENAME4',3)
INSERT INTO #TEMP2 VALUES(1,'LNAME1',4)
INSERT INTO #TEMP2 VALUES(2,'LNAME2',4)
INSERT INTO #TEMP2 VALUES(1,'LNAME5',5)

Now i need the following result

MDATE,STIDCOUNT,MIDCOUNT,(OEND-OSTART),(DDPM-DAAM),MSTOP,MMILES,MHRS,SH
08/02/2011,5,1,1055,13:15,10,77056,42:16,1
08/03/2011,1,1,700,08:00,2,656,07:00,0
08/03/2011,3,1,65,12:30,5,46056,24:19,1
08/04/2011,6,2,13606,14:30,8,125122,20:11,1

The columns are separated by commas.IF the SH flag is diiferent in one date we can show it as two rows.IF the SH flag is same in one date we can show a single row.

Please help me.

Kunjappy

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-08-08 : 07:53:59

something like


SELECT  t1.MDATE,
COUNT(t2.STID) AS STIDCOUNT,
COUNT(t1.MID) AS MIDCOUNT,
MAX([(OEND-OSTART)]) AS [(OEND-OSTART)],
CONVERT(varchar(8),DATEADD(mi,MAX([(DDPM-DAAM)]),0),108) AS [(DDPM-DAAM)],
MAX(MSTOP) AS MSTOP,
MAX(MMILES) AS MMILES,
CAST(MAX(MHRS)/60 AS varchar(3)) + ':' + CAST(MAX(MHRS)%60 AS varchar(3))  AS MHRS,
t1.SH
FROM (SELECT MID,MDATE,SH,SUM(OEND-OSTART) AS [(OEND-OSTART)],
SUM(DATEDIFF(mi,DAAM,DDPM)) AS [(DDPM-DAAM)],SUM(MSTOP) AS MSTOP,
SUM(MMILES*1) AS MMILES,
SUM(LEFT(MHRS,2)*60 + RIGHT(MHRS,2)) AS MHRS
FROM   #TEMP1 
GROUP BY MID,MDATE,SH
)t1
JOIN #TEMP2 t2
ON t2.MID = t1.MID
GROUP BY t1.MDATE,t1.SH

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

anish20in
Starting Member

18 Posts

Posted - 2011-08-08 : 10:00:19

MIDCOUNT is wrong.It should be
1 (08/02/2011)
1 (08/03/2011)
1 (08/03/2011)
2 (08/04/2011)

Also 4th row is not correct
Now getting
08/04/2011,6,6,10551,08:00:00,6,104656,12:11,1
But i want 4th row as
08/04/2011,6,2,13606,14:30,8,125122,20:11,1

Kunjappy

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-08-08 : 13:40:36

[code]
SELECT t1.MDATE,
COUNT(t2.STID) AS STIDCOUNT,
COUNT(DISTINCT t1.MID) AS MIDCOUNT,
SUM([(OEND-OSTART)]) AS [(OEND-OSTART)],
CONVERT(varchar(8),DATEADD(mi,SUM([(DDPM-DAAM)]),0),108) AS [(DDPM-DAAM)],
SUM(MSTOP) AS MSTOP,
SUM(MMILES) AS MMILES,
CAST(SUM(MHRS)/60 AS varchar(3)) + ':' + CAST(SUM(MHRS)%60 AS varchar(3)) AS MHRS,
t1.SH
FROM (SELECT MID,MDATE,SH,SUM(OEND-OSTART) AS [(OEND-OSTART)],
SUM(DATEDIFF(mi,DAAM,DDPM)) AS [(DDPM-DAAM)],SUM(MSTOP) AS MSTOP,
SUM(MMILES*1) AS MMILES,
SUM(LEFT(MHRS,2)*60 + RIGHT(MHRS,2)) AS MHRS
FROM #TEMP1
GROUP BY MID,MDATE,SH
)t1
JOIN #TEMP2 t2
ON t2.MID = t1.MID
GROUP BY t1.MDATE,t1.SH
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

anish20in
Starting Member

18 Posts

Posted - 2011-08-09 : 03:00:05

Again the result is wrong.First three column is correct.
As I ran your query it get the result as
2011-08-02,5,1,5275,18:15:00,50,385280,211:20,1
2011-08-03,1,1,700,08:00:00,2,656,7:0,0
2011-08-03,3,1,195,13:30:00,15,138168,72:57,1
2011-08-04,6,2,48314,18:00:00,28,291176,64:44,1

Here OEND-OSTART of First row must be (2101-1046)=1055,but in your result it is 5275 and also other fields.
The correct result which i need is given earlier.

Kunjappy

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-08-09 : 10:02:34

[code]
SELECT t1.MDATE,
COUNT(t2.STID) AS STIDCOUNT,
COUNT(DISTINCT t1.MID) AS MIDCOUNT,
MAX([(OEND-OSTART)]) AS [(OEND-OSTART)],
CONVERT(varchar(8),DATEADD(mi,SUM([(DDPM-DAAM)]),0),108) AS [(DDPM-DAAM)],
SUM(MSTOP) AS MSTOP,
SUM(MMILES) AS MMILES,
CAST(SUM(MHRS)/60 AS varchar(3)) + ':' + CAST(SUM(MHRS)%60 AS varchar(3)) AS MHRS,
t1.SH
FROM (SELECT MID,MDATE,SH,SUM(OEND-OSTART) AS [(OEND-OSTART)],
SUM(DATEDIFF(mi,DAAM,DDPM)) AS [(DDPM-DAAM)],SUM(MSTOP) AS MSTOP,
SUM(MMILES*1) AS MMILES,
SUM(LEFT(MHRS,2)*60 + RIGHT(MHRS,2)) AS MHRS
FROM #TEMP1
GROUP BY MID,MDATE,SH
)t1
JOIN #TEMP2 t2
ON t2.MID = t1.MID
GROUP BY t1.MDATE,t1.SH

[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

anish20in
Starting Member

18 Posts

Posted - 2011-08-09 : 10:32:26

Again the result is not correct.I need the following

MDATE,STIDCOUNT,MIDCOUNT,(OEND-OSTART),(DDPM-DAAM),MSTOP,MMILES,MHRS,SH
08/02/2011,5,1,1055,13:15,10,77056,42:16,1
08/03/2011,1,1,700,08:00,2,656,07:00,0
08/03/2011,3,1,65,12:30,5,46056,24:19,1
08/04/2011,6,2,13606,14:30,8,125122,20:11,1

The bolder part is different when i ran the query and compare with your result.
Please let me know know if any clarification needed.

Thanks
Kunjappy

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources