How to display monthly total record? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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 2005 Forums

Transact-SQL (2005)

How to display monthly total record?

Author

Topic

dishaniti
Starting Member

3 Posts

Posted - 2008-11-21 : 15:21:41

Hello All

In following I am displaying total no of records from various table in 1 row for the month of October(10). Now I want to display the record of year 2008 monthwise means 1 record for each month. How Can I do this?
*----------------------------------
select (select count(*) from table1
where year(uploaDdate) = 2008 and month(uploaddate) = 10 and source = 'H' ) as table1,
(select count(*) from table2
where year(uploaDdate) = 2008 and month(uploaddate) = 10 and source = 'H' ) as table2 ,
(select count(*) from table3
where year(uploaDdate) = 2008 and month(uploaddate) = 10 and source = 'H' ) as table3 ,
(select count(*) from table4
where year(uploaDdate) = 2008 and month(uploaddate) = 10 and source = 'H') as table4
*----------------------------------

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2008-11-21 : 15:37:41

select 
	year(uploaDdate),month(uploaDdate),count(*) from
(
	select * from Table1 where source = 'H' 
	union all
	select * from Table1 where source = 'H' 
	union all
	...........
)t
group by year(uploaDdate),month(uploaDdate)

dishaniti
Starting Member

3 Posts

Posted - 2008-11-21 : 15:43:53

have you tested this solution, record will not come in following format

MOnth - Table1 - table2 - table3
----------------------------------
September - 12 - 34 -22
October - 2 - 4 -5
November - 1 - 4 -8

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-11-21 : 18:50:06

[code]SELECT theMonthName,
SUM(table1) AS table1,
SUM(table1) AS table2,
SUM(table1) AS table3,
SUM(table1) AS table4
FROM (
SELECT DATEPART(MONTH, uploadDate) AS theMonthPart,
DATENAME(MONTH, uploadDate) AS theMonthName,
COUNT(*) AS table1,
0 AS table2,
0 AS table3,
0 AS table4
FROM Table1
WHERE uploadDate >= '20080101'
AND uploadDate < '20090101'
AND source = 'H'
GROUP BY DATEPART(MONTH, uploadDate),
DATENAME(MONTH, uploadDate)

UNION ALL

SELECT DATEPART(MONTH, uploadDate) AS theMonthPart,
DATENAME(MONTH, uploadDate) AS theMonthName,
0 AS table1,
COUNT(*) AS table2,
0 AS table3,
0 AS table4
FROM Table2
WHERE uploadDate >= '20080101'
AND uploadDate < '20090101'
AND source = 'H'
GROUP BY DATEPART(MONTH, uploadDate),
DATENAME(MONTH, uploadDate)

UNION ALL

SELECT DATEPART(MONTH, uploadDate) AS theMonthPart,
DATENAME(MONTH, uploadDate) AS theMonthName,
0 AS table1,
0 AS table2,
COUNT(*) AS table3,
0 AS table4
FROM Table3
WHERE uploadDate >= '20080101'
AND uploadDate < '20090101'
AND source = 'H'
GROUP BY DATEPART(MONTH, uploadDate),
DATENAME(MONTH, uploadDate)

UNION ALL

SELECT DATEPART(MONTH, uploadDate) AS theMonthPart,
DATENAME(MONTH, uploadDate) AS theMonthName,
0 AS table1,
0 AS table2,
0 AS table3,
COUNT(*) AS table4
FROM Table4
WHERE uploadDate >= '20080101'
AND uploadDate < '20090101'
AND source = 'H'
GROUP BY DATEPART(MONTH, uploadDate),
DATENAME(MONTH, uploadDate)
) AS d
GROUP BY theMonthPart,
theMonthName
ORDER BY theMonthPart[/code]

E 12°55'05.63"
N 56°04'39.26"

dishaniti
Starting Member

3 Posts

Posted - 2008-11-24 : 12:30:53

Hello Peso

Thank you very much, it works very charmingly. The only thing I want to know If I run this query for 2 year span, it club the record, I want to display by year wise monthly record.

Is this possible?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-24 : 12:35:58

[code]SELECT theYearPart,
theMonthName,
SUM(table1) AS table1,
SUM(table1) AS table2,
SUM(table1) AS table3,
SUM(table1) AS table4
FROM (
SELECT DATEPART(MONTH, uploadDate) AS theMonthPart,
DATENAME(MONTH, uploadDate) AS theMonthName,
DATEPART(YEAR, uploadDate) AS theYearPart,
COUNT(*) AS table1,
0 AS table2,
0 AS table3,
0 AS table4
FROM Table1
WHERE uploadDate >= '20080101'
AND uploadDate < '20090101'
AND source = 'H'
GROUP BY DATEPART(MONTH, uploadDate),
DATENAME(MONTH, uploadDate),
DATEPART(YEAR, uploadDate)

UNION ALL

SELECT DATEPART(MONTH, uploadDate) AS theMonthPart,
DATENAME(MONTH, uploadDate) AS theMonthName,
DATEPART(YEAR, uploadDate) AS theYearPart,
0 AS table1,
COUNT(*) AS table2,
0 AS table3,
0 AS table4
FROM Table2
WHERE uploadDate >= '20080101'
AND uploadDate < '20090101'
AND source = 'H'
GROUP BY DATEPART(MONTH, uploadDate),
DATENAME(MONTH, uploadDate),
DATEPART(YEAR, uploadDate)

UNION ALL

SELECT DATEPART(MONTH, uploadDate) AS theMonthPart,
DATENAME(MONTH, uploadDate) AS theMonthName,
DATEPART(YEAR, uploadDate) AS theYearPart,
0 AS table1,
0 AS table2,
COUNT(*) AS table3,
0 AS table4
FROM Table3
WHERE uploadDate >= '20080101'
AND uploadDate < '20090101'
AND source = 'H'
GROUP BY DATEPART(MONTH, uploadDate),
DATENAME(MONTH, uploadDate),
DATEPART(YEAR, uploadDate)

UNION ALL

SELECT DATEPART(MONTH, uploadDate) AS theMonthPart,
DATENAME(MONTH, uploadDate) AS theMonthName,
DATEPART(YEAR, uploadDate) AS theYearPart,
0 AS table1,
0 AS table2,
0 AS table3,
COUNT(*) AS table4
FROM Table4
WHERE uploadDate >= '20080101'
AND uploadDate < '20090101'
AND source = 'H'
GROUP BY DATEPART(MONTH, uploadDate),
DATENAME(MONTH, uploadDate),
DATEPART(YEAR, uploadDate)
) AS d
GROUP BY theYearPart,theMonthPart,
theMonthName
ORDER BY the YearPart,theMonthPart
[/code]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources