SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Can i title rows as well as columns?
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

jimtimber
Yak Posting Veteran

United Kingdom
60 Posts

Posted - 06/20/2014 :  17:21:16  Show Profile  Reply with Quote
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
Constraint Violating Yak Guru

USA
437 Posts

Posted - 06/20/2014 :  17:56:23  Show Profile  Reply with Quote


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


Edited by - ScottPletcher on 06/20/2014 17:56:45
Go to Top of Page

jimtimber
Yak Posting Veteran

United Kingdom
60 Posts

Posted - 06/20/2014 :  18:00:35  Show Profile  Reply with Quote
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
Go to Top of Page

jimtimber
Yak Posting Veteran

United Kingdom
60 Posts

Posted - 06/21/2014 :  10:03:44  Show Profile  Reply with Quote
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
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 06/21/2014 :  15:46:49  Show Profile  Reply with Quote
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
Go to Top of Page

jimtimber
Yak Posting Veteran

United Kingdom
60 Posts

Posted - 06/21/2014 :  16:35:10  Show Profile  Reply with Quote
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
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000