SQL Sum in Multiple join query

Question

I'm struggling for the setup for this. This is the setup of the first table. The first query needs to only apply to items past a certain date, say 2012-06-01.

|                  Table: TIMELOG                  |
| INQUIRY_ID | … |      LOGMINS    | … |  OPERID   | … |    EDATE    |
|     2      |   |       45        |   |  bob      |   | 2012-05-01  |
|     5      |   |       3         |   |  richard  |   | 2012-06-02  |
|     5      |   |       12        |   |  bob      |   | 2012-07-01  |
|     5      |   |       15        |   |  paul     |   | 2012-07-01  |
|     6      |   |       10        |   |  paul     |   | 2012-07-01  |

The query then needs to go into a different table using INQUIRY_ID and get some additional fields (there is only one entry per INQUIRY_ID in this next table:

|                  Table: INQUIRY                  |
| INQUIRY_ID | … |  CATEGORY_ID    | … |  PROD_ID  |
|     2      |   |       45        |   |  6        |
|     3      |   |       3         |   |  50       |
|     4      |   |       12        |   |  3        |
|     5      |   |       15        |   |  67       |
|     6      |   |       10        |   |  2        |

Once we have the CATEGORY_ID and PROD_ID the "friendly names" for those are in their own seperate tables

|            Table: CATEGORY            |
| CATEGORY_ID | … |      CATNAME        |
|     45      |   |       Server        |
|     3       |   |       Workstation   |
|     12      |   |       Phones        |
|     15      |   |       Backup        |  
|     10      |   |       Network       |

|            Table: PROD                |
| PROD_ID     | … |      PRODDESC       |
|     6       |   |       SBS 2003      |
|     50      |   |       Windows 7     |
|     3       |   |       iPhone        |
|     67      |   |       Buexec        |  
|     2       |   |       SwitchF       |

I'd then like to total the LOGMINS field, bearing in mind sometimes it will have multiple LOGMINS per INQUIRY_ID To finish up as

| INQUIRY_ID | … |      TOTAL      | … |  CATNAME         | … |   PRODDESC    |
|     2      |   |       45        |   |     Server       |   |   SBS 2003    |
|     5      |   |       30        |   |     Workstation  |   |   Windows 7   |
|     6      |   |       10        |   |     Phones       |   |   iPhone      |

I'm sorry for the extraordinary amount of detail, the queries I have tried just bomb out, my skill in SQL blows up when I get into nested queries and multiple joins. Any help would be massively appreciated.

Query I am running now:

 select 
 timelog.INQUIRY_ID,
 SUM (logmins) AS Total,
 catname,
 proddesc,
 EDATE
from
 timelog
inner join inquiry on timelog.inquiry_id=inquiry.inquiry_id
inner join category on inquiry.category_id = category.category_id
inner join prod on inquiry.prod_id = prod.prod_id
where EDATE > '2013-07-01'
group by
timelog.INQUIRY_ID,
catname,
proddesc,
edate
order by timelog.INQUIRY_ID desc

podiluska · Accepted Answer · 2013-08-02 15:57:05Z

1

Join the tables your data is in together, then group on the data that you are keeping constant, and sum the fields you want to add

select 
  inquiry.inquiry_id,
  SUM(logmins),
  catname,
  proddesc
from
   timelog
    inner join inquiry on timelog.inquiry_id=inquiry.inquiry_id
    inner join category on inquiry.category_id = category.category_id
    inner join prod on inquiry.prod_id = prod.prod_id
group by
  inquiry.inquiry_id,
  catname,
  proddesc

edited Aug 2, 2013 at 15:57

answered Aug 2, 2013 at 15:13

podiluska

51.6k7 gold badges102 silver badges105 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

Trinitrotoluene Over a year ago

That works but it is pulling back multiple INQUIRY_ID in the results that are the same, what I need to end up with is it totalling all the rows that have the SAME inquiry_ID and just adding them in as one total per inquiry_ID. Is this possible? Thanks for your help

podiluska Over a year ago

@Trinitrotoluene Have you added some other fields, or are there other differences between the rows?

Trinitrotoluene Over a year ago

Hi, sorry this is my fault I should have been more specific. Some of the tables I outlined before have a good deal more columns with more data in that I don't need, if that's what you're asking?

podiluska Over a year ago

@Trinitrotoluene The query as detailed above should return a single row for each combination of enquiry/catname/proddesc. Can you give an example of where that isn't the case?

Trinitrotoluene Over a year ago

I've updated original question with the query I am running, this returns for example: 45558 26 Backup Configuration 2013-08-02 13:41:06.000 45558 3 Backup Configuration 2013-08-02 13:41:17.000 those being two rows as an example, sorry for formatting

|

Collectives™ on Stack Overflow

SQL Sum in Multiple join query

1 Answer 1

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related