January 21, 2013 at 2:48 pm
Currently I have this query.
SELECT C.listcode, C.year, COUNT(NamesTest.year) as Mailed
FROM
(
SELECT * FROM
(SELECT DISTINCT listcode FROM NamesTest) as A
CROSS JOIN
(SELECT DISTINCT year FROM NamesTest) as B
) as C
LEFT JOIN NamesTest ON C.listcode = NamesTest.listcode AND C.year = NamesTest.year
Where c.Year > 2007
GROUP BY c.listcode, c.year
Order By c.listcode, c.year
I want to add the count results of this query to it:
SELECT listcode, year, COUNT(year) AS Sold
FROM forteInfotest
GROUP BY listcode, year
But every time I add a join the count from the forteInfotest table is the same as the other count. Can anyone help me. Thanks in advance!
January 21, 2013 at 3:33 pm
Doesn't sound too tough. Please take a look at the first link in my signature for best practices when posting questions. Once we have the necessary information you will find lots of people around here willing and able to help pretty quickly.
_______________________________________________________________
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/
January 22, 2013 at 1:20 am
-- To obtain accurate rowcounts from the two tables, you need to aggregate them separately
;WITH Matrix (listcode,[year]) AS (
(SELECT DISTINCT listcode FROM NamesTest) as A
CROSS JOIN
(SELECT DISTINCT [year] FROM NamesTest WHERE [Year] > 2007) as B
)
SELECT mx.listcode, mx.[year], n.Mailed, f.Sold
FROM Matrix mx
LEFT JOIN ( -- Using COUNT with a column name can be confusing, avoid - unless also specifying DISTINCT
SELECT listcode, [year], COUNT(*) AS Mailed
FROM NamesTest
GROUP BY listcode, [year]
) n
LEFT JOIN (
SELECT listcode, [year], COUNT(*) AS Sold
FROM forteInfotest
GROUP BY listcode, [year]
) f
Without any data to test against (see Sean's post above), this is a best guess.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
January 22, 2013 at 6:36 am
Ok I did not know I needed to post any data. Here is a little bit:
Listcode Year Mailed
102EL 2008 11488
102EL 2009 11305
102EL 2010 3607
102EL 2011 46
170MD2008 25657
170MD2009 0
170MD2010 15131
170MD2011 9039
That is the results of the first query. Here is the second:
listcodeyear Sold
102EL 2006 1
102EL 2007 13
102EL 2008 2
102EL 2009 11
102EL 2010 3
102EL 2011 1
Does that help?
January 22, 2013 at 6:42 am
ncurran217 (1/22/2013)
Ok I did not know I needed to post any data. ..
No worries, you're new here. Have a read of this article[/url], it will help you formulate some test data.
Have you tried the query I posted above?
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
January 22, 2013 at 6:48 am
Sorry yes I tried your query you created and it did not work. Gave me three errors on three different lines:
Msg 156, Level 15, State 1, Line 2
Incorrect syntax near the keyword 'as'.
Msg 156, Level 15, State 1, Line 4
Incorrect syntax near the keyword 'as'.
Msg 102, Level 15, State 1, Line 17
Incorrect syntax near 'f'.
I am reading the link of Forum Etiquette and trying to find where to get to the Text Mode of Query Analyzer, to be able to put the sample data in here. Sorry for the headaches.
January 22, 2013 at 6:59 am
Also, to get the numbers I posted as a result from my query I would have to post data with 100k rows.
January 22, 2013 at 7:08 am
ncurran217 (1/22/2013)
Also, to get the numbers I posted as a result from my query I would have to post data with 100k rows.
Here's how to create some sample data - and with it a working query 😉
SELECT *
INTO #namesTest
FROM (
SELECT Listcode = '102EL', [Year] = '2008'
FROM (SELECT TOP(11488) n = 1 from sys.columns a, sys.columns b) d
UNION ALL
SELECT '102EL', '2009'
FROM (SELECT TOP(11305) n = 1 from sys.columns a, sys.columns b) d
UNION ALL
SELECT '102EL', '2010'
FROM (SELECT TOP(3607) n = 1 from sys.columns a, sys.columns b) d
UNION ALL
SELECT '102EL', '2011'
FROM (SELECT TOP(46) n = 1 from sys.columns a, sys.columns b) d
UNION ALL
SELECT '170MD', '2008'
FROM (SELECT TOP(25657) n = 1 from sys.columns a, sys.columns b) d
--SELECT '170MD', '2009' 0
UNION ALL
SELECT '170MD', '2010'
FROM (SELECT TOP(15131) n = 1 from sys.columns a, sys.columns b) d
UNION ALL
SELECT '170MD', '2011'
FROM (SELECT TOP(9039) n = 1 from sys.columns a, sys.columns b) d
) q
-- (76273 row(s) affected)
SELECT *
INTO #forteInfotest
FROM (
SELECT listcode = '102EL', [year] = '2006'
FROM (SELECT TOP(1) n = 1 from sys.columns a, sys.columns b) d
UNION ALL
SELECT '102EL', '2007'
FROM (SELECT TOP(13) n = 1 from sys.columns a, sys.columns b) d
UNION ALL
SELECT '102EL', '2008'
FROM (SELECT TOP(2) n = 1 from sys.columns a, sys.columns b) d
UNION ALL
SELECT '102EL', '2009'
FROM (SELECT TOP(11) n = 1 from sys.columns a, sys.columns b) d
UNION ALL
SELECT '102EL', '2010'
FROM (SELECT TOP(3) n = 1 from sys.columns a, sys.columns b) d
UNION ALL
SELECT '102EL', '2011'
FROM (SELECT TOP(1) n = 1 from sys.columns a, sys.columns b) d
) q
-- (31 row(s) affected)
-- query
;WITH Matrix (listcode,[year]) AS
(
SELECT *
FROM
(SELECT DISTINCT listcode FROM #NamesTest) a
CROSS JOIN
(SELECT DISTINCT [year] FROM #NamesTest WHERE [Year] > 2007) b
)
SELECT mx.listcode, mx.[year], n.Mailed, f.Sold
FROM Matrix mx
LEFT JOIN ( -- Using COUNT with a column name can be confusing, avoid - unless also specifying DISTINCT
SELECT listcode, [year], COUNT(*) AS Mailed
FROM #NamesTest
GROUP BY listcode, [year]
) n ON n.Listcode = mx.listcode AND n.year = mx.year
LEFT JOIN (
SELECT listcode, [year], COUNT(*) AS Sold
FROM #forteInfotest
GROUP BY listcode, [year]
) f ON f.Listcode = mx.listcode AND f.year = mx.year
ORDER BY mx.listcode, mx.[year]
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
January 22, 2013 at 7:23 am
Alright, that worked. How did you get the code for the sample data or did you just type that all up? Also, is there something simple to show the Nulls as 0 instead?
January 22, 2013 at 7:26 am
Have a look at the code I used for the sample data, if there's anything you are unsure of then ask. Note that the sample data tables I created for this are #temp tables, you will need to remove the # from my code for it to work against your tables.
Use ISNULL() to deal with nulls in the output.
Cheers
ChrisM
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
January 22, 2013 at 7:28 am
Ok thanks again!
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply