Solved

SQL query to sub-total data

Posted on 2014-10-07
10
156 Views
Last Modified: 2014-10-26
I have a very basic understanding of SQL and need to modify a query which someone else created.  The current query shows the number of clients added to our database within a time window.  We now want to sub-total this query by branch, by quarter.

The current query is this:

declare     @start_date datetime

      select      @start_date = dateadd(mm,-24,getdate())

      select
            @start_date                       as period_desc,
            office_name,
            c.client_org_name                 as parent_company,
            a.client_org_name                 as company,
            a.address1                        as address1,
            a.address2                        as address2,
            a.city                            as city,
            a.pincode                         as pincode,
            a.state                           as state,
            a.contact_first_name              as contact_first_name,
            a.contact_last_name               as contact_last_name,
            a.contact_phone                   as contact_phone,
            a.client_id                       as client_id,
            a.created_on                      as created_on,
            isnull(count(d.group_id),0)         as number_of_orders

      from  client_organization     a,
            branch_offices           b,
            client_organization     c,
            orders                  d

      where b.office_id =  a.office_id
      and   c.group_id  =  a.client_id
      and   d.group_id  =* a.group_id
      and   a.created_on >= @start_date

      group by office_name, c.client_org_name, a.client_org_name, a.address1, a.address2, a.city, a.pincode, a.state,
             a.contact_first_name, a.contact_last_name, a.contact_phone, a.client_id, a.created_on

      order by office_name, parent_company, company


Can anyone assist in breaking the details down to sub-total this, as requested.

Any assistance is appreciated.
0
Comment
Question by:DrakeCA
  • 5
  • 5
10 Comments
 
LVL 46

Expert Comment

by:Vitor Montalvão
ID: 40366218
If the field CREATEDON is date data type than this should work:
declare     @start_date datetime

select      @start_date = dateadd(mm,-24,getdate())

select
     @start_date                       as period_desc,
     office_name,
     c.client_org_name                 as parent_company,
     a.client_org_name                 as company,
     a.address1                        as address1,
     a.address2                        as address2,
     a.city                            as city,
     a.pincode                         as pincode,
     a.state                           as state,
     a.contact_first_name              as contact_first_name,
     a.contact_last_name               as contact_last_name,
     a.contact_phone                   as contact_phone,
     a.client_id                       as client_id,
     DATEPART (QUARTER, a.created_on)  as created_on,
     isnull(count(d.group_id),0)       as number_of_orders
from  client_organization     a,
     branch_offices           b,
     client_organization     c,
     orders                  d
where b.office_id =  a.office_id
and   c.group_id  =  a.client_id
and   d.group_id  =* a.group_id
and   a.created_on >= @start_date
group by office_name, c.client_org_name, a.client_org_name, a.address1, a.address2, a.city, a.pincode, a.state,
      a.contact_first_name, a.contact_last_name, a.contact_phone, a.client_id
order by office_name, parent_company, company

Open in new window

0
 

Author Comment

by:DrakeCA
ID: 40366245
This tells me the quarter the order was created on, but doesn't provide any sub-totals per branch, per quarter.

How do I get that additional information?
0
 
LVL 46

Expert Comment

by:Vitor Montalvão
ID: 40366266
Do you need to present all those columns? If not you can reduce It only to those one that really matters. Like this:
declare     @start_date datetime

select      @start_date = dateadd(mm,-24,getdate())

select
     @start_date                       as period_desc,
     office_name,
     DATEPART (QUARTER, a.created_on)  as created_on,
     isnull(count(d.group_id),0)       as number_of_orders
from  client_organization     a,
     branch_offices           b,
     client_organization     c,
     orders                  d
where b.office_id =  a.office_id
and   c.group_id  =  a.client_id
and   d.group_id  =* a.group_id
and   a.created_on >= @start_date
group by office_name
order by office_name

Open in new window

0
 

Author Comment

by:DrakeCA
ID: 40366322
Sure, we can minimize the report to only those fields, just to pull this particular information.

How would I subtotal it by branch, per quarter then?
0
 
LVL 46

Expert Comment

by:Vitor Montalvão
ID: 40366334
Isn't the number of orders that you want (it's the last column in the SELECT fields)?
If not just replace it for the field that you want. And the operation also (COUNT or SUM)?
declare     @start_date datetime

select      @start_date = dateadd(mm,-24,getdate())

select
     @start_date                       as period_desc,
     office_name,
     DATEPART (QUARTER, a.created_on)  as created_on,
     count(d.group_id)       as number_of_orders
from  client_organization     a,
     branch_offices           b,
     client_organization     c,
     orders                  d
where b.office_id =  a.office_id
and   c.group_id  =  a.client_id
and   d.group_id  =* a.group_id
and   a.created_on >= @start_date
group by office_name
order by office_name

Open in new window

0
DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

 

Author Comment

by:DrakeCA
ID: 40366428
I'm not looking for the total number of orders.  I'm trying to determine the total number of clients added to the system.  Technically every client should have a unique ID number.  I assume I would count based on that ID number?

When I post the query you have listed I get an error stating "Msg 8120, Level 16, State 1, Line 5
Column 'client_organization.created_on' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause."


How do I get rid of that error?  I tried adding "created_on" to the group_by line, but it tells me it's an ambiguous column name.
0
 
LVL 46

Expert Comment

by:Vitor Montalvão
ID: 40366450
Ok. Now we have something. Try this one:
declare     @start_date datetime

select      @start_date = dateadd(mm,-24,getdate())

select
     @start_date                       as period_desc,
     office_name,
     DATEPART (QUARTER, a.created_on)  as created_on,
     count(a.client_id)       as number_of_clients
from  client_organization     a,
     branch_offices           b,
     client_organization     c,
     orders                  d
where b.office_id =  a.office_id
and   c.group_id  =  a.client_id
and   d.group_id  =* a.group_id
and   a.created_on >= @start_date
group by office_name, DATEPART (QUARTER, a.created_on)
order by office_name

Open in new window

0
 

Accepted Solution

by:
DrakeCA earned 0 total points
ID: 40395713
I ended up using the query below:

use olo_na

declare     @start_date datetime
select      @start_date = dateadd(mm,-24,getdate())

SELECT office_name, DATEPART(YEAR,a.created_on) [Year],
DATEPART(QUARTER,a.created_on) [Quarter],
COUNT(1) [created_on]
FROM client_organization a
left join branch_offices  b on b.office_id =  a.office_id
where a.created_on >= @start_date
GROUP BY office_name, DATEPART(YEAR,a.created_on),DATEPART(QUARTER,a.created_on)
ORDER BY 1,2,3
0
 
LVL 46

Expert Comment

by:Vitor Montalvão
ID: 40396289
Even I didn't give you the solution, maybe for lack of information from you, your solution was based on mine.
0
 

Author Closing Comment

by:DrakeCA
ID: 40404575
This is the solution
0

Featured Post

3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
xpath sql query 2008 8 42
triggered use of sp_send_dbmail failure 2 22
SQL Login 17 38
SQL - SP needs a little help 9 19
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

911 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now