adey khader adey khader - 6 months ago 10
SQL Question

Can I make one select statement in SQL?

SELECT Count(*) AS MonthTotal
FROM CRMProjects
WHERE CreatedDate between '01 May 2016' and '31 May 2016'

SELECT Count(*) AS YearTotal
FROM CRMProjects
WHERE CreatedDate between '01 Jan 2016' and '31 Dec 2016'

SELECT Count(*) AS MonthNew
FROM CRMProjects
WHERE CreatedDate between '01 May 2016' and '31 May 2016'
AND SystemType = 'O'

SELECT Count(*) AS YearClosed
FROM CRMProjects
WHERE CreatedDate between '01 Jan 2016' and '31 Dec 2016'
AND SystemType = 'C'


It only populates the month in the table, it does not populate the other sections as Visual studio does not allow multiple select statements for one data set.

Answer

I would suggest something like this:

SELECT 'Month Total' AS Label, Count(*) AS Value
FROM CRMProjects
WHERE CreatedDate between '01 May 2016' and '31 May 2016'

UNION ALL

SELECT 'Year Total' AS Label, Count(*) AS Value
FROM CRMProjects
WHERE CreatedDate between '01 Jan 2016' and '31 Dec 2016'

UNION ALL

SELECT 'Month New' AS Label, Count(*) AS Value
FROM CRMProjects
WHERE CreatedDate between '01 May 2016' and '31 May 2016'
AND SystemType = 'O'

UNION ALL

SELECT 'Year Closed' AS Label, Count(*) AS Value
FROM CRMProjects
WHERE CreatedDate between '01 Jan 2016' and '31 Dec 2016'
AND SystemType = 'C'