[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 753
  • Last Modified:

union query alternative

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
j2jake
Asked:
j2jake
  • 4
  • 3
  • 3
  • +1
1 Solution
 
Luke ChungPresidentCommented:
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
 
GRayLCommented:
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
 
zadeveloperCommented:
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
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
j2jakeAuthor Commented:
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
 
GRayLCommented:
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
 
Luke ChungPresidentCommented:
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
 
j2jakeAuthor Commented:
Hi GRayL,

Awesome! It worked a lot faster. Thanks!

Hi LukeChung-FMS,

Can you please give me an example?
0
 
Luke ChungPresidentCommented:
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
 
j2jakeAuthor Commented:
Thanks!
0
 
j2jakeAuthor Commented:
Thanks everyone!
0
 
GRayLCommented:
Thanks, glad to help
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 4
  • 3
  • 3
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now