homepage Welcome to WebmasterWorld Guest from 54.167.174.90
register, free tools, login, search, pro membership, help, library, announcements, recent posts, open posts,
Become a Pro Member
Home / Forums Index / Code, Content, and Presentation / Databases
Forum Library, Charter, Moderator: open

Databases Forum

    
Merging 3 Queries Into One
Trying to condense mysql queries
teamcoltra

5+ Year Member



 
Msg#: 4356477 posted 12:02 am on Aug 30, 2011 (gmt 0)

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

WebmasterWorld Senior Member 5+ Year Member



 
Msg#: 4356477 posted 3:06 am on Aug 30, 2011 (gmt 0)

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

5+ Year Member



 
Msg#: 4356477 posted 3:07 pm on Aug 30, 2011 (gmt 0)

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

WebmasterWorld Senior Member 5+ Year Member



 
Msg#: 4356477 posted 9:45 pm on Aug 30, 2011 (gmt 0)

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

5+ Year Member



 
Msg#: 4356477 posted 6:58 pm on Sep 3, 2011 (gmt 0)

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

WebmasterWorld Senior Member 5+ Year Member



 
Msg#: 4356477 posted 12:31 am on Sep 5, 2011 (gmt 0)

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

5+ Year Member



 
Msg#: 4356477 posted 8:24 pm on Sep 9, 2011 (gmt 0)

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

WebmasterWorld Senior Member 5+ Year Member



 
Msg#: 4356477 posted 2:20 am on Sep 19, 2011 (gmt 0)

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.

Global Options:
 top home search open messages active posts  
 

Home / Forums Index / Code, Content, and Presentation / Databases
rss feed

All trademarks and copyrights held by respective owners. Member comments are owned by the poster.
Home ¦ Free Tools ¦ Terms of Service ¦ Privacy Policy ¦ Report Problem ¦ About ¦ Library ¦ Newsletter
WebmasterWorld is a Developer Shed Community owned by Jim Boykin.
© Webmaster World 1996-2014 all rights reserved