Please start any new threads on our new site at http://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | 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?
 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
Aged Yak Warrior

USA
550 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
52326 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  
 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.06 seconds. Powered By: Snitz Forums 2000