?
Solved

union query alternative

Posted on 2010-01-09
11
Medium Priority
?
750 Views
Last Modified: 2013-11-27
Hi Experts!

I have a crosstab with the following data - customer id as the row, department as the column, customer count as the value. I need to determine the total count using two departments. for example, i need to get the total count of both the shoppers from men's and women's, then men's and boys, then men's and girls and a lot more.

the thing here is i have a union query but it it is taking a while. i am interested if there could be another solution for this.

here's a sample of my union query
SELECT "*Total Distinct" AS Department, COUNT (*) AS Count
FROM qryXtab AS q
UNION 
SELECT "Women's and Men's", COUNT (*)
FROM qryXtab AS q
WHERE q.[women's] <> 0 AND q.[men's] <> 0;
UNION 
SELECT "Women's and Young Men's", COUNT (*)
FROM qryXtab AS q
WHERE q.[women's] <> 0 AND q.[young men's] <> 0;
UNION 
SELECT "Women's and Juniors'", COUNT (*)
FROM qryXtab AS q
WHERE q.[women's] <> 0 AND q.[juniors'] <> 0;

Open in new window

0
Comment
Question by:j2jake
[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
  • 4
  • 3
  • 3
  • +1
11 Comments
 
LVL 10

Expert Comment

by:Luke Chung
ID: 26277714
Use a SELECT query on the CROSSTAB query to display the fields you want and create calculated fields on the fields you want to add together.
0
 
LVL 44

Expert Comment

by:GRayL
ID: 26279302
Can you show us a few records from the cross tab query?  I'm having a hard time 'visualizing' what you are trying to do.  From what I can see, all the COUNT (*) will give you in your second query is the number of records that two different departments were not zero, not the number of customers in both departments.  It would also help if you could post the cross tab query?
0
 
LVL 13

Expert Comment

by:zadeveloper
ID: 26279340
try this:

SELECT [Department], [Count]
FROM 
   (
		SELECT 
			sum (1) as [*Total Distinct],
			sum (case when [women's] <> 0 AND [men's] <> 0 then 1 else 0 end) as [Women's and Men's],
			sum (case when [women's] <> 0 AND [young men's] <> 0 then 1 else 0 end) as [Women's and Young Men's],
			sum (case when [women's] <> 0 AND [juniors'] <> 0 then 1 else 0 end) as [Women's and Juniors']		
		FROM
			qryXtab
 ) t
UNPIVOT
   ([Count] FOR [Department] IN 
      ( [*Total Distinct], [Women's and Men's], [Women's and Young Men's], [Women's and Juniors'])
)AS unpvt
order by [Department]

Open in new window

0
Percona Live Europe 2017 | Sep 25 - 27, 2017

The Percona Live Open Source Database Conference Europe 2017 is the premier event for the diverse and active European open source database community, as well as businesses that develop and use open source database software.

 

Author Comment

by:j2jake
ID: 26280627
Hi GRayL

Here is a sample of the crosstab result:

customer | total of cid_count | Boys | Girls | Women's | Men's |
233          | 4                           | 1       | 1      | 1              | 1        |
6314        | 2                           | 1       |         | 1              |           |
8365        | 1                           |          |         | 1              |           |

I have attached the crosstab query too. I have 2 tables, tblDept and tblTrail. The nested sql will get all the data in tblTrail and add the dept that corresponds to the category code. Then the crosstab query will run through that data. this was first conceptualized in excel. i created a pivot table from my data having the dept as the column headers and the customer_id as the rows then did a sumproduct. the problem with excel is that i have more than 1M rows so i have to use access.

Hi zadeveloper,

Isnt UNPIVOT only in SQL Server. My application is in ACCESS. Any suggestions?

Hi Luke,

still a newbie here. can you suggest how can i develop a better SELECT query?


TRANSFORM Count(cid_count) AS CountOfcid_count
SELECT customer_id AS customer, Count(cid_count) AS [Total Of cid_count]
FROM (SELECT t.customer_id, t.category_code AS category_code, d.department AS department, t.customer_id AS cid_count
FROM tblTrail AS t LEFT JOIN tblDept AS d ON t.category_code = d.product_category
GROUP BY t.month_year, t.customer_id, t.category_code, d.department
)  AS query
GROUP BY customer_id
PIVOT department;

Open in new window

0
 
LVL 44

Accepted Solution

by:
GRayL earned 500 total points
ID: 26283886
From what I can see customer_id and cid_count are the same thing.  Try this:

TRANSFORM Count(t.Customer_ID) AS CustCount
SELECT t.Customer_ID, Count(t.Customer_ID) AS TotalCustCount
FROM tblTrail AS t Left JOIN tblDept AS d on t.Category_Code = d.Product_Category
GROUP BY t.Customer_ID
PIVOT d.Department;

I think you have unnecessarily bogged things down by adding the sub-query.
0
 
LVL 10

Expert Comment

by:Luke Chung
ID: 26284557
Isn't this just running the crosstab query and adding up a few of the columns into new columns?  If so, this should be simply be a select query on the crosstab query.  Really simple.
0
 

Author Comment

by:j2jake
ID: 26307687
Hi GRayL,

Awesome! It worked a lot faster. Thanks!

Hi LukeChung-FMS,

Can you please give me an example?
0
 
LVL 10

Expert Comment

by:Luke Chung
ID: 26307972
Not sure what additional information you need.  Are you familiar with creating a select query on a table and creating a calculated field by adding two fields?
If so, instead of using a table as the query's datasource, use your crosstab query and do the same thing.
Here's a paper I've written covering lots of query basics and tips: http://www.fmsinc.com/tpapers/queries/index.html
0
 

Author Closing Comment

by:j2jake
ID: 31675139
Thanks!
0
 

Author Comment

by:j2jake
ID: 26318126
Thanks everyone!
0
 
LVL 44

Expert Comment

by:GRayL
ID: 26319291
Thanks, glad to help
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

In earlier versions of Windows (XP and before), you could drag a database to the taskbar, where it would appear as a taskbar icon to open that database.  This article shows how to recreate this functionality in Windows 7 through 10.
Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…
Suggested Courses

801 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