September 11, 2012 at 7:30 am
Hi,
I am obviously doing something wrong, and hopefully someone will be kind enough to educate me.
query below:
SELECT format(WEEK_DATE, "YYYYMM") as f1, Count(TBL_AML_ERRORS.Q6_BRANCH) AS CountOfQ6_BRANCH
FROM TBL_AML_ERRORS
GROUP BY format(WEEK_DATE, "YYYYMM")
UNION
SELECT format(WEEK_DATE, "YYYYMM") as f1, Count(TBL_AML_ERRORS_ARCHIVE.Q6_BRANCH) AS CountOfQ6_BRANCH
FROM TBL_AML_ERRORS_ARCHIVE
GROUP BY format(WEEK_DATE, "YYYYMM")
WEEK_DATE contains proper dates
I am hoping to get a result set back in the form of:
201208 100
201207 234
201206 132
etc ....
Instead, when dates for same month appear in both tables, I get:
201208 100
201207 234
201206 131
201206 1
etc ....
So, what's wrong with that query (i.e. me), why does it not group and sum those rows?
Is it because it is going "well, from the first table you get 201206 131 and the second table 201206 1 - these are different rows, so you get two rows" ....
If that's the case, how would I achieve a total across both tables?
Cheers,
B
September 11, 2012 at 7:39 am
Hi
You could try this:
WITH
CTE
AS
(
SELECT format(WEEK_DATE, "YYYYMM") as f1, Count(TBL_AML_ERRORS.Q6_BRANCH) AS CountOfQ6_BRANCH
FROM TBL_AML_ERRORS
GROUP BY format(WEEK_DATE, "YYYYMM")
UNION
SELECT format(WEEK_DATE, "YYYYMM") as f1, Count(TBL_AML_ERRORS_ARCHIVE.Q6_BRANCH) AS CountOfQ6_BRANCH
FROM TBL_AML_ERRORS_ARCHIVE
GROUP BY format(WEEK_DATE, "YYYYMM")
)
SELECT
f1
,SUM(CountOfQ6_BRANCH)
FROM
CTE
GROUP BY
f1
Andy
==========================================================================================================================
A computer lets you make more mistakes faster than any invention in human history - with the possible exceptions of handguns and tequila. Mitch Ratcliffe
September 11, 2012 at 7:40 am
What about?
SELECT f1, COUNT( Q6_BRANCH) AS CountOfQ6_BRANCH
FROM (
SELECT format(WEEK_DATE, "YYYYMM") as f1,
TBL_AML_ERRORS.Q6_BRANCH
FROM TBL_AML_ERRORS
UNION ALL
SELECT format(WEEK_DATE, "YYYYMM") as f1,
TBL_AML_ERRORS_ARCHIVE.Q6_BRANCH
FROM TBL_AML_ERRORS_ARCHIVE
) tbl
GROUP BY f1
EDIT: Error fix
September 11, 2012 at 7:43 am
SELECT f1, COUNT(*) AS CountOfQ6_BRANCH
FROM (
SELECT format(WEEK_DATE, "YYYYMM") as f1 FROM TBL_AML_ERRORS
WHERE Q6_BRANCH IS NOT NULL
UNION ALL
SELECT format(WEEK_DATE, "YYYYMM") as f1 FROM TBL_AML_ERRORS_ARCHIVE
WHERE Q6_BRANCH IS NOT NULL
) u
GROUP BY f1
Or , if you really need to know the count of distinct branches per week:
SELECT f1, COUNT(*) AS CountOfQ6_BRANCH
FROM (
SELECT format(WEEK_DATE, "YYYYMM") as f1, Q6_BRANCH
FROM TBL_AML_ERRORS WHERE Q6_BRANCH IS NOT NULL
UNION
SELECT format(WEEK_DATE, "YYYYMM") as f1, Q6_BRANCH
FROM TBL_AML_ERRORS_ARCHIVE WHERE Q6_BRANCH IS NOT NULL
) u
GROUP BY f1
September 11, 2012 at 7:44 am
Luis Cazares (9/11/2012)
What about?
SELECT f1, COUNT( Q6_BRANCH) AS CountOfQ6_BRANCH
FROM (
SELECT format(WEEK_DATE, "YYYYMM") as f1,
TBL_AML_ERRORS.Q6_BRANCH
FROM TBL_AML_ERRORS
GROUP BY format(WEEK_DATE, "YYYYMM")
...
This one will produce error...
September 11, 2012 at 7:47 am
You're right Eugene, I missed that part and only deleted the last group by.
I could have avoided that with something to test with.
September 11, 2012 at 7:49 am
Thanks a lot guys (haven't tested anything yet)! .. posted topic, went to get a coffee ... came back, refresh page --> 5 replies 🙂 ... I LOVE the SQL-family!
B
September 11, 2012 at 7:56 am
SELECT
f1, CountOfQ6_BRANCH = SUM(CountOfQ6_BRANCH)
FROM (
SELECT format(WEEK_DATE, "YYYYMM") as f1, Count(Q6_BRANCH) AS CountOfQ6_BRANCH
FROM TBL_AML_ERRORS
GROUP BY format(WEEK_DATE, "YYYYMM")
UNION ALL -- the keyword ALL is important if you don't want to eliminate dupes
-- from the second query which exist in the first, you want to SUM them instead
SELECT format(WEEK_DATE, "YYYYMM") as f1, Count(Q6_BRANCH) AS CountOfQ6_BRANCH
FROM TBL_AML_ERRORS_ARCHIVE
GROUP BY format(WEEK_DATE, "YYYYMM")
) d
GROUP BY f1
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
September 11, 2012 at 8:03 am
Thanks amillion guys - went for the code from Luis Cazares ... works like a charm as far as I can tell 🙂
B
September 11, 2012 at 8:50 am
bleroy (9/11/2012)
Thanks amillion guys - went for the code from Luis Cazares ... works like a charm as far as I can tell 🙂B
It does, after he fixed it to look like mine one 😉
No offence, Luis...
September 11, 2012 at 9:05 am
Eugene Elutin (9/11/2012)
bleroy (9/11/2012)
Thanks amillion guys - went for the code from Luis Cazares ... works like a charm as far as I can tell 🙂B
It does, after he fixed it to look like mine one 😉
No offence, Luis...
It really looks like yours and you might even say that it's the same, even if I didn't see yours before.
September 11, 2012 at 9:08 am
Luis Cazares (9/11/2012)
Eugene Elutin (9/11/2012)
bleroy (9/11/2012)
Thanks amillion guys - went for the code from Luis Cazares ... works like a charm as far as I can tell 🙂B
It does, after he fixed it to look like mine one 😉
No offence, Luis...
It really looks like yours and you might even say that it's the same, even if I didn't see yours before.
So, did you or you did not? Any witnesses :hehe:
September 11, 2012 at 9:15 am
Did not. I found out the query has an important difference (WHERE clause) that I'm not sure how would it affect the performance (I'm sure it depends on the data).
I only saw your observation on my mistake.
September 11, 2012 at 9:30 am
well I think you are both winners 😉
September 11, 2012 at 9:33 am
bleroy (9/11/2012)
well I think you are both winners 😉
I'm only joking, really. The "winner" here is not paid much... (I would say no paid at all :-))
I wonder which query type will run faster? The one which SUMs result of UNION which pre-count the Branches in two tables, or the one which UNIONs all and COUNT's once.
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply