Can i title rows as well as columns? - 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 2008 Forums

Transact-SQL (2008)

Can i title rows as well as columns?

Author

Topic

jimtimber
Yak Posting Veteran

60 Posts

Posted - 2014-06-20 : 17:21:16

Hi,

I'm new to SQL so excuse me if posted in the wrong forum.

I'm wondering if its possible to give rows set names, like I can name Columns. Specifically, I've done a count of how many enquiries my company gets each month:

SELECT COUNT(DateNotified) AS [Monthly Enquiries]
FROM dbo.Tbl_Families
WHERE(YEAR(DateNotified) = '2013')
GROUP BY MONTH(DateNotified)

This brings out:
Monthly Enquiries
408
327
214
661
641
463
605
373
850
339
427
529

I want to add "Jan", "Feb", "Mar" etc. going downwards in a column before this. Is this possible? I intend to add other columns after this one to include "Monthly Sales" "Monthly Revenue".

Is this possible? I really hope so!

Thanks in advance,

Jim

Jim

ScottPletcher
Aged Yak Warrior

550 Posts

Posted - 2014-06-20 : 17:56:23

[code]

SELECT
LEFT(DATENAME(MONTH, MonthNotified), 3) AS Month,
[Monthly Enquiries]
FROM (
SELECT
DATEADD(MONTH, DATEDIFF(MONTH, 0, DateNotified), 0) AS MonthNotified,
COUNT(DateNotified) AS [Monthly Enquiries]
FROM dbo.Tbl_Families
WHERE DateNotified >= '20130101' AND DateNotified < '20140101'
GROUP BY DATEADD(MONTH, DATEDIFF(MONTH, 0, DateNotified), 0)
) AS subquery1
ORDER BY MonthNotified

[/code]

jimtimber
Yak Posting Veteran

60 Posts

Posted - 2014-06-20 : 18:00:35

quote:

Originally posted by ScottPletcher



SELECT
    LEFT(DATENAME(MONTH, MonthNotified), 3) AS Month,
    [Monthly Enquiries]
FROM (
    SELECT 
        DATEADD(MONTH, DATEDIFF(MONTH, 0, DateNotified), 0) AS MonthNotified,
        COUNT(DateNotified) AS [Monthly Enquiries]
    FROM dbo.Tbl_Families
    WHERE DateNotified >= '20130101' AND DateNotified < '20140101'
    GROUP BY DATEADD(MONTH, DATEDIFF(MONTH, 0, DateNotified), 0)
) AS subquery1
ORDER BY MonthNotified

THANK YOU SO MUCH! This has took me hours to figure out, I was no where near. Thank you!!

Jim

jimtimber
Yak Posting Veteran

60 Posts

Posted - 2014-06-21 : 10:03:44

Hi Scott, if I'm wanting to add further columns with the same counts-per-month from different tables, whats the best way of editing the code? I want to add number of enquiries via website which is stored in Tbl_EnquiryDetails.ApplicantSource (source being 'Website')?

Thank you!

J.

Jim

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2014-06-21 : 15:46:49

quote:
Originally posted by jimtimber

Hi Scott, if I'm wanting to add further columns with the same counts-per-month from different tables, whats the best way of editing the code? I want to add number of enquiries via website which is stored in Tbl_EnquiryDetails.ApplicantSource (source being 'Website')?

Thank you!

J.

Jim

You also need to specify how tables are related to Tbl_families table, Based on that relationship you've to apply grouping on other tables and do aggregation like SUM,COUNT etc

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

jimtimber
Yak Posting Veteran

60 Posts

Posted - 2014-06-21 : 16:35:10

Thanks Visqkh,

In columns next to what Scott did for the count of enquiries by month I will need:

Column 2: Total number of sales per month in the year (Tbl_Sales.SaleID COUNT of SaleID). Each sale has a date linked to it on Tbl_Sales.Date

Column 3: Total number of sales via website enquiries per month in the year (Tbl_Families.EnquirySource = 'Website', COUNT of Tbl_Families.EnquiryDate)

Column 4: Total Brochures sent to enquirers per month in the year (Tbl_Familes.DatePromotionSent COUNT DatePromotionSent)

I also need to have 2 columns for our 2 offices. So:

Column 5: Count of Sales, London (Tbl_Sales.Office = LOND
Column 6: Count of Sales, Leeds (Tbl_Sales.Office = LEED

For the last 2, I would need to count the salesID each month dependent on what office sold it.

A bit complex considering I've use SQL for 3 weeks. My colleague is off sick so I'm having to learn quickly.

Jim

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources