Counting additional Columns in MS SQL Server

Databases
1

I have a database with the following columns:
Source Group, Source, TotalLeadsPerSource, Data

The totalleads, show the total leads for each source per date.

Here is some sample data:
SourceGroup Source TotalLeads Date
PRINT Working_World 5 Nov 10 2010 12:00AM
DIRECT WebSiteHomePage 3 Nov 10 2010 12:00AM
TV TV 3 Nov 10 2010 12:00AM
OTHER REFERRAL 10 Nov 10 2010 12:00AM
PRINT RED PLUM FRONT CVR 4 Nov 10 2010 12:00AM
INTERNET RecruitMilitary 8 Nov 10 2010 12:00AM
INTERNET PS SML AD #2 3 Nov 10 2010 12:00AM
PRINT PS SML AD 3 Nov 10 2010 12:00AM
PRINT PS INSERTS 23 Nov 10 2010 12:00AM
PRINT PS FRONT CVR ONL 1 Nov 10 2010 12:00AM
PRINT PS FRONT CVR CMPS 11 Nov 10 2010 12:00AM

PRINT Working_World 5 Nov 11 2010 12:00AM
DIRECT WebSiteHomePage 3 Nov 11 2010 12:00AM
TV TV 3 Nov 11 2010 12:00AM
OTHER REFERRAL 10 Nov 11 2010 12:00AM
PRINT RED PLUM FRONT CVR 4 Nov 11 2010 12:00AM
INTERNET RecruitMilitary 8 Nov 11 2010 12:00AM
INTERNET PS SML AD #2 3 Nov 11 2010 12:00AM
PRINT PS SML AD 3 Nov 11 2010 12:00AM
PRINT PS INSERTS 23 Nov 11 2010 12:00AM
PRINT PS FRONT CVR ONL 1 Nov 11 2010 12:00AM
PRINT PS FRONT CVR CMPS 11 Nov 11 2010 12:00AM

Each Source belongs to a certain SourceGroup.
I would like to add a column to show the total for each SourceGroup per Date (SourceGroupSummary
), in the same report. such that the result data looks like this:

SourceGroup    SourceGroupSummary    Source    TotalLeads    Date
PRINT    50            Nov 10 2010 12:00AM
        RED PLUM FRONT CVR    4    Nov 10 2010 12:00AM
        Working_World    5    Nov 10 2010 12:00AM
        PS SML AD    3    Nov 10 2010 12:00AM
        PS INSERTS    23    Nov 10 2010 12:00AM
        PS FRONT CVR ONL    1    Nov 10 2010 12:00AM
        PS FRONT CVR CMPS    11    Nov 10 2010 12:00AM
DIRECT    3            Nov 10 2010 12:00AM
        WebSiteHomePage    3    Nov 10 2010 12:00AM
TV    3            Nov 10 2010 12:00AM
        TV    3    Nov 10 2010 12:00AM
OTHER    10            Nov 10 2010 12:00AM
        REFERRAL    10    Nov 10 2010 12:00AM
INTERNET    11            Nov 10 2010 12:00AM
        RecruitMilitary    8    Nov 10 2010 12:00AM
        PS SML AD #2    3    Nov 10 2010 12:00AM
                
PRINT    43            Nov 11 2010 12:00AM
        Working_World    5    Nov 11 2010 12:00AM
        PS SML AD    3    Nov 11 2010 12:00AM
        PS INSERTS    23    Nov 11 2010 12:00AM
        PS FRONT CVR ONL    1    Nov 11 2010 12:00AM
        PS FRONT CVR CMPS    11    Nov 11 2010 12:00AM
DIRECT    3            Nov 11 2010 12:00AM
        WebSiteHomePage    3    Nov 11 2010 12:00AM
TV    3            Nov 11 2010 12:00AM
        TV    3    Nov 11 2010 12:00AM
OTHER    10            Nov 11 2010 12:00AM
        REFERRAL    10    Nov 11 2010 12:00AM
PRINT    4            
        RED PLUM FRONT CVR    4    Nov 11 2010 12:00AM
INTERNET    11            
        RecruitMilitary    8    Nov 11 2010 12:00AM
        PS SML AD #2    3    Nov 11 2010 12:00AM

Can this be achieved in a single query?

2

probably

could you please reformat your data into columns?

i can’t understand it the way it shows up now

3

What is the best way to do that? I copy and pasted from excel.
Attached is how I’d like the data to look.

4

Here is the original table as a tab deliminated file.

5

by pulling out the tabs and inserting spaces, like this –


SourceGroup  Source                 TotalLeads Date
PRINT        Working_World             5       Nov 10 2010
DIRECT       WebSiteHomePage           3       Nov 10 2010
TV           TV                        3       Nov 10 2010
OTHER        REFERRAL                 10       Nov 10 2010
PRINT        RED PLUM FRONT CVR        4       Nov 10 2010
INTERNET     RecruitMilitary           8       Nov 10 2010
INTERNET     PS SML AD #2              3       Nov 10 2010
PRINT        PS SML AD                 3       Nov 10 2010
PRINT        PS INSERTS               23       Nov 10 2010
PRINT        PS FRONT CVR ONL          1       Nov 10 2010
PRINT        PS FRONT CVR CMPS        11       Nov 10 2010

PRINT        Working_World             5       Nov 11 2010
DIRECT       WebSiteHomePage           3       Nov 11 2010
TV           TV                        3       Nov 11 2010
OTHER        REFERRAL                 10       Nov 11 2010
PRINT        RED PLUM FRONT CVR        4       Nov 11 2010
INTERNET     RecruitMilitary           8       Nov 11 2010
INTERNET     PS SML AD #2              3       Nov 11 2010
PRINT        PS SML AD                 3       Nov 11 2010
PRINT        PS INSERTS               23       Nov 11 2010
PRINT        PS FRONT CVR ONL          1       Nov 11 2010
PRINT        PS FRONT CVR CMPS        11       Nov 11 2010

see? a lot easier to understand :slight_smile:

your attachments didn’t do this, they were just as unformatted as your original posting

now, as for your requested output, you can get the results you want, but ~not~ formatted the way you want, you will have to do the report formatting in your application language

SELECT 'D' AS rowtype -- details
     , SourceGroup 
     , Source 
     , TotalLeads 
     , Date
  FROM daTable
UNION ALL
SELECT 'T'           -- totals
     , SourceGroup 
     , NULL 
     , SUM(TotalLeads) 
     , Date
  FROM daTable
GROUP
    BY SourceGroup 
     , Date
ORDER
    BY SourceGroup 
     , Date
     , rowtype DESC -- totals before details
6

A big THANK YOU to r937. Using your query saved me a lot of time (and multiple queries!)

I’ve noted your advice regarding pasting tables too. :slight_smile: