August 19, 2013 at 1:41 pm
I am trying to join three tables together to get a sum from one table, count from second table, and the third table is just a reference. So here is some examples of my tables:
Listcode Table:
Listcode DNIS
AM 5425566
EL 5425563
Calls Table:
Date DNIS Offered
7/20/2013 5425566 125
7/20/2013 5425563 75
7/19/2013 5425566 123
Sales Table:
purchdate Listcode
7/20/13 AM
7/20/13 AM
7/20/13 AM
7/20/13 EL
7/19/13 AM
7/19/13 EL
Now you can probably tell which columns match which between the three. So now what I am trying to do is have a query, which I am trying to put into PHP, I want to select Listcode "AM". Here is what my query results I want to see:
Date Offered Sales
7/20/13 125 3
7/19/13 123 1
I cannot seem to join or get selects within a join to work. Any help would be appreciated! Let me know if there is any other information needed?
August 19, 2013 at 1:48 pm
You have abstracted your problem so far that there is not much detail left. Do you actually store aggregate data in the calls table or is it really a table with a bunch of rows that get counted?
The basic gist of your query is this:
select Date, Offered, count(purchased.primarykey) as Sales
from Yourtables
where Listcode = 'AM'
group by Date, Offered
If you need more specific help, we will need a few things:
1. Sample DDL in the form of CREATE TABLE statements
2. Sample data in the form of INSERT INTO statements
3. Expected results based on the sample data
Please take a few minutes and read the first article in my signature for best practices when posting questions.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
August 19, 2013 at 1:58 pm
Figured it out.
August 19, 2013 at 2:10 pm
ncurran217 (8/19/2013)
Figured it out.
Glad you figured it out. Remember that when you post it isn't like we want to see all the columns to be annoying. We don't need the columns not involved with the issue. The problem is that from what you posted there just wasn't enough detail to offer much more than the shot in the dark I took.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
August 19, 2013 at 2:13 pm
Ok, just thought with the detail I gave was enough to understand the issue at hand. I didn't think that posting SQL statements for tables and data would really be worth it and just jumble up the question at hand. I will take that into consideration next time. Thanks though.
August 19, 2013 at 3:30 pm
You'll find out that posting DDL, sample data and expected results all in a consumable format that we can copy and execute without anyother effort will give you better and faster answers and won't make your post as long if you put everything within the proper IFCodes located to the left of the screen when you're writing your post. Check the [ code="sql"][/code] to have a nice presentation for it.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply