Merging 3 Queries Into One - Databases forum at WebmasterWorld - WebmasterWorld

Forum Moderators: open

Message Too Old, No Replies

Merging 3 Queries Into One

Trying to condense mysql queries

teamcoltra

12:02 am on Aug 30, 2011 (gmt 0)

10+ Year Member

The goal is to run a single query that will search column A looking for a defined parameter, and then search column B looking for another parameter.

It will then total the numbers up for me for the report.

My current code however runs them as THREE separate queries.

Does anyone know a way I can combine the code to get the result I want?

Please and thanks!

SELECT COUNT(*) FROM sales WHERE dnucxtype='Digital' AND dnuinstall='Retail Pick-up' AND dnuoutcome='Accepted';
SELECT COUNT(*) FROM sales WHERE dnucxtype='Digital' AND dnuinstall='Tech Install' AND dnuoutcome='Accepted';
SELECT COUNT(*) FROM sales WHERE dnucxtype='Digital' AND dnuinstall='Direct Fullfill' AND dnuoutcome='Accepted';


SELECT COUNT(*) FROM sales WHERE dnucxtype='Analog' AND dnuinstall='Retail Pick-up' AND dnuoutcome='Accepted';
SELECT COUNT(*) FROM sales WHERE dnucxtype='Analog' AND dnuinstall='Tech Install' AND dnuoutcome='Accepted';
SELECT COUNT(*) FROM sales WHERE dnucxtype='Analog' AND dnuinstall='Direct Fullfill' AND dnuoutcome='Accepted';

Dijkgraaf

3:06 am on Aug 30, 2011 (gmt 0)

WebmasterWorld Senior Member

10+ Year Member

Not quite sure what you are aiming for, but you can combine the three as below.

SELECT COUNT(*) FROM sales WHERE dnucxtype='Digital' AND dnuinstall in ('Retail Pick-up','Tech Install',Direct Fullfill') AND dnuoutcome='Accepted';

SELECT COUNT(*) FROM sales WHERE dnucxtype='Analog' AND dnuinstall in ('Retail Pick-up','Tech Install',Direct Fullfill') AND dnuoutcome='Accepted';

teamcoltra

3:07 pm on Aug 30, 2011 (gmt 0)

10+ Year Member

That gives me the total of everything, my goal was the total of each but in one query.

so the result would be something more like:
digital
Retail Pick-up | Tech Install | Direct Fulfill
10010 | 28320 | 83838

Dijkgraaf

9:45 pm on Aug 30, 2011 (gmt 0)

WebmasterWorld Senior Member

10+ Year Member

If you want them on separate lines
e.g.
Retail Pick-up | 10010
Tech Install | 28320
Direct Fulfill | 83838
you can use the following.

SELECT dnuinstall, Count(1)
FROM sales WHERE dnucxtype='Digital' AND dnuinstall in ('Retail Pick-up','Tech Install',Direct Fullfill') AND dnuoutcome='Accepted';
GROUP BY dnuinstall

If that is not what you are after you need to do nested selects

teamcoltra

6:58 pm on Sep 3, 2011 (gmt 0)

10+ Year Member

SELECT dnucity as dnucity, dnuinstall, count(1) from sales where dnuinstall='Tech Install' OR dnuinstall='Direct Fullfill' OR dnuinstall='Retail Pick-Up' GROUP BY dnucity

what you are suggesting results in [paste2.org...] while I was looking for a result more like [paste2.org...]

Dijkgraaf

12:31 am on Sep 5, 2011 (gmt 0)

WebmasterWorld Senior Member

10+ Year Member

Except your query doesn't look at dnuoutcome or dnucxtype as per your original queries.

Please re-state what you want as you original request was unclear.

teamcoltra

8:24 pm on Sep 9, 2011 (gmt 0)

10+ Year Member

What I want as the result is:
For each dnucity it then gives the total of each of the dnucxtypes.

So if we have
City A - Tech Install
City A - Direct Fulfil
City B - Direct Fulfil
City A - Tech Install
City C - Retail Pickup
City B - Tech Install
It will give:

City A - Tech Install 2
City A - Direct Fulfil 1
City B - Tech install 1
City B - Direct Fulfil 1
City C - Retail Pickup 1

I have done it before, but I have lost what I used... and forget what I did.

[paste2.org...] This gives an example of the output though.

Dijkgraaf

2:20 am on Sep 19, 2011 (gmt 0)

WebmasterWorld Senior Member

10+ Year Member

SELECT dnucity, dnucxtype, count(*) AS cnt
FROM sales
WHERE dnuinstall in ('Retail Pick-up','Tech Install',Direct Fullfill')
AND dnuoutcome='Accepted'
GROUP BY dnucity, dnucxtype

I've assumed you still want to limit it to the three types of dnuinstall and dnuoutcome of Accepted.
If not just alter/remove that part of the where clause.