Solved

SQL query to sub-total data

Posted on 2014-10-07
10
164 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 5
10 Comments
 
LVL 49

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 49

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
Free Webinar: AWS Backup & DR

Join our upcoming webinar with experts from AWS, CloudBerry Lab, and the Town of Edgartown IT to discuss best practices for simplifying online backup management and cutting costs.

 

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 49

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
 

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 49

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 49

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

Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

Question has a verified solution.

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

Suggested Solutions

PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
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…
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

730 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