Database

SELECT stu  
FROM student  
WHERE  id IN (     
	 SELECT sid    
	 FROM [student-subject]
	 GROUP BY sid  
	 HAVING COUNT(*) < (SELECT COUNT(*) FROM subject)   
	) 

prithaa wrote:

But I am not going to have more than 10 products .
Is the Pivot Tables a link .
But it does not work

CREATE TABLE #p (ProdID int, ProdName nvarchar(20)); 
CREATE TABLE #s (TimePeriod int, Qty int, ProdID int); 

insert into #p (ProdID, ProdName) select 1, 'Shirts'; 
insert into #p (ProdID, ProdName) select 2, 'Trousers'; 
insert into #p (ProdID, ProdName) select 3, 'Tie'; 
insert into #p (ProdID, ProdName) select 4, 'Belts'; 

insert into #s (TimePeriod, Qty, ProdID) select 20080705,138,1; 
insert into #s (TimePeriod, Qty, ProdID) select 20080706,539,1; 
insert into #s (TimePeriod, Qty, ProdID) select 20080707,313,1; 
insert into #s (TimePeriod, Qty, ProdID) select 20080705,660,2; 
insert into #s (TimePeriod, Qty, ProdID) select 20080706,370,2; 
insert into #s (TimePeriod, Qty, ProdID) select 20080707,574,2; 
insert into #s (TimePeriod, Qty, ProdID) select 20080705,764,3; 
insert into #s (TimePeriod, Qty, ProdID) select 20080706,294,3; 
insert into #s (TimePeriod, Qty, ProdID) select 20080707,202,3; 
insert into #s (TimePeriod, Qty, ProdID) select 20080705,59,4; 
insert into #s (TimePeriod, Qty, ProdID) select 20080706,247,4; 
insert into #s (TimePeriod, Qty, ProdID) select 20080707,731,4; 


DECLARE @cmd NVARCHAR(max); 
SET @cmd = 'SELECT TimePeriod ' +
( SELECT ' , SUM(['+CONVERT(NVARCHAR,ProdID)+']) AS ['+ ProdName +']'
  FROM #p AS p
  FOR XML PATH('')
)
+
' FROM( 
	SELECT #s.TimePeriod, #s.Qty, #p.ProdID, #p.ProdName 
	FROM #p 
	INNER JOIN #s on #p.ProdID = #s.ProdID 
  ) p 
PIVOT ( 
	SUM( Qty ) 
	FOR ProdID IN 
	( [0] '+ 
( SELECT ' , ['+CONVERT(NVARCHAR,ProdID)+']' 
  FROM #p AS p 
  FOR XML PATH('') 
) 
+ 
'   ) 
  ) AS pvt 
GROUP BY TimePeriod 
ORDER BY TimePeriod ; ' ; 

PRINT @cmd; 
EXEC(@cmd); 

DROP TABLE #p; 
DROP TABLE #s; 

<br />

INSERT INTO #s ( TimePeriod,  Qty, ProdID ) 
       SELECT      20080705,  138,      1  ;

INSERT INTO #s ( TimePeriod,  Qty, ProdID ) 
       VALUES  (   20080705,  138,      1 );

BalasubramanianK wrote:

In my table
Date Amount TotalAmount
01/07/2008 10000 10000
01/07/2008 10000 5000
01/07/2008 10000 0

I am using the following queries separately.

Select Date, Count(*), Sum(Amount) From Table Group by Date

Select Date, Count(*), Sum(Amount) From Table Where Amount>TotalAmount And TotalAmount <> 0 Group by Date

Select Date, Count(*), Sum(Amount) From Table Where Amount>TotalAmount And TotalAmount = 0 Group by Date

How can I execute the above 3 mentioned separate queries in a single query to get the result like

Date TotalRecords Amount1 TotalRec Amount2 Tot Amount2
01/07/2008 3 30000 1 10000 1 1000