MySQL - Combining multiple queries + counts

Question

I'm attempting to combine a few queries and can't seem to nail it down. I was wondering if someone could point me in the right direction.

Here are the statements:

SELECT
I.id,
I.custname,
I.custemail,
I.sku,
DATE_FORMAT(FROM_UNIXTIME(I.ts), '%l:%i:%s %p, %c/%e/%Y') AS ts
FROM images I
WHERE I.stat = 0

SELECT
COUNT(*) AS total1
FROM images
WHERE stat = 1 AND sku = ?

SELECT
COUNT(*) AS total2
FROM images
WHERE stat = 1 
AND sku IN (SELECT subsku FROM combo WHERE sku = ?)

Right now I'm using the 3 separate queries and am using code to add the two totals and display them. But I now need to be able to sort by the sum of the totals, so I'd like to get all of that data into one statement.. something like:

SELECT
I.id,
I.custname,
I.custemail,
I.sku,
DATE_FORMAT(FROM_UNIXTIME(I.ts), '%l:%i:%s %p, %c/%e/%Y') AS ts,
SUM(total1+total2)
FROM images I
WHERE I.stat = 0

But I'm unsure how to do that. I tried the code below, but it failed:

SELECT 
I.id, 
I.custname, 
I.custemail, 
I.sku, 
DATE_FORMAT(FROM_UNIXTIME(I.ts),'%l:%i:%s %p, %c/%e/%Y') AS ts, 
(
    SELECT COUNT(*) AS total1 FROM images WHERE stat = 1 AND (
        sku IN (
            SELECT subsku FROM combo WHERE sku = I.sku
    ) OR sku = I.sku)
) AS skuct 
FROM images I 
WHERE stat = 0

Any help would be greatly appreciated. Many thanks!

UPDATE

First off thanks to everyone who has offered assistance. I've been working on the query and think I'm getting closer, but I'm now hitting a 'subquery returns more than 1 row' error:

SELECT 
I.id, 
I.custname, 
I.custemail, 
I.sku,
DATE_FORMAT(FROM_UNIXTIME(I.ts), '%l:%i:%s %p, %c/%e/%Y') AS ts,
(
    SELECT COUNT(*)
    FROM images
    WHERE stat = 1 
    AND sku = I.sku
    OR sku IN(
        SELECT subsku FROM combo WHERE sku = I.sku
    )
GROUP BY sku
) AS total
FROM images I
WHERE stat = 0

The problem is that the subquery SELECT subsku FROM combo WHERE... returns a resultset (0+ rows) vs a scalar. If I can figure out that part, I think this will work.

Yes, I'd like to be able to ORDER BY the SUM of the totals — greatcaesarsghost
– greatcaesarsghost, Commented May 2, 2012 at 20:02

Thomas · Accepted Answer · 2012-05-04 21:01:00Z

2

Select I.id, I.custname, I.custemail, I.sku
    , DATE_FORMAT(FROM_UNIXTIME(I.ts), '%l:%i:%s %p, %c/%e/%Y') AS ts
    ,   (
        Select Sum( Case
                    When I1.sku = ? Then 1
                    When I1.sku In( Select subsku From combo As S1 Where S1.sku = ? ) Then 1
                    Else 0
                    End ) As Total
        From images As I1
        Where I1.stat = 1
        ) As Total
From images As I
Where stat = 0

Another solution

Select I.id, I.custname, I.custemail, I.sku
    , DATE_FORMAT(FROM_UNIXTIME(I.ts), '%l:%i:%s %p, %c/%e/%Y') AS ts
    ,   (
        Select Count(*)
        From images As I1
        Where I1.stat = 1
            And (
                I1.sku = ?
                Or I1.sku In ( Select subsku From combo As S1 Where S1.sku = ? )
                )
        ) As Total
From images As I
Where stat = 0

Addition

Another possible solution:

Select I.id, I.custname, I.custemail, I.sku
    , DATE_FORMAT(FROM_UNIXTIME(I.ts), '%l:%i:%s %p, %c/%e/%Y') AS ts
    ,   (
        Select Sum( Cnt )
        From    (
                Select Count(*) As Cnt
                From images As I1
                    Left Join combo As C1
                        On C1.sku = I1.sku
                Where I1.stat = 1
                    And I1.sku = ?
                    And C1.PrimaryKeyCol Is Null
                Union All 
                Select Count( Distinct I1.PrimaryKeyCol )
                From images As I1
                    Join combo As C1
                        On C1.sku = I1.sku
                Where I1.stat = 1
                    And I1.sku = ?
                ) As Z
        ) As Total
From images As I
Where stat = 0

Edit

If you are looking for the count by image in which you correlate the counts to the outer table images sku column, that's entirely different. For that, I would use a derived table:

Select I.id, I.custname, I.custemail, I.sku
    , DATE_FORMAT(FROM_UNIXTIME(I.ts), '%l:%i:%s %p, %c/%e/%Y') AS ts
    , Counts.Total
From images As I
    Join    (
            Select Z.sku, Sum(Z.Cnt) As Total
            From    (
                    Select I1.sku, Count(*) As Cnt
                    From images As I1
                        Left Join combo As C1
                            On C1.sku = I1.sku
                    Where I1.stat = 1
                        And C1.PrimaryKeyCol Is Null
                    Group By I1.sku
                    Union All 
                    Select I1.sku, Count( Distinct I1.PrimaryKeyCol )
                    From images As I1
                        Join combo As C1
                            On C1.sku = I1.sku
                    Where I1.stat = 1
                    Group By I1.sku
                    ) As Z
            Group By Z.sku
            )  As Counts
        On Counts.sku = I.sku
Where stat = 0

Obviously in all cases, replace PrimaryKeyCol with the name of the actual primary key column of the images table.

edited May 4, 2012 at 21:01

answered May 2, 2012 at 19:22

Thomas

64.8k12 gold badges98 silver badges142 bronze badges

Sign up to request clarification or add additional context in comments.

11 Comments

greatcaesarsghost Over a year ago

This works (although I had to remove the S1. from the second WHEN), however it takes a very long time to run (~80s). EXPLAIN shows an index on 'sku' but it's still pulling all of the rows twice.

Thomas Over a year ago

@greatcaesarsghost - I've corrected the alias (S1) problem which was a typo. The alias was supposed to be on the table combo not the column. I've also posted another solution.

greatcaesarsghost Over a year ago

Apologies for not getting back to you sooner. I was out yesterday. The second solution works, but it's still too slow (90+s). I'm changing the "?"s to I.sku, though, as I won't be passing a variable (I just need the totals in the result set). That's no doubt what's slowing it. I'm just not sure how to go about doing it. Currently leaning towards just tossing it all in an array and sorting on a key.

Thomas Over a year ago

@greatcaesarsghost - Is there an index on images.sku, images.stat and combo.subsku?

greatcaesarsghost Over a year ago

Good question! I had to check. The following indexes exist: images(sku), images(stat), images(sku,stat), combo(subsku). But no multi-column/multi-table indexes. Is it even possible to index across tables without some foreign key?

|

Arth · Accepted Answer · 2012-05-02 19:16:29Z

1

Have you considered changing the WHERE logic to perform the count in one. Assuming the two counts are mutually exclusive you could just OR the two conditions.

answered May 2, 2012 at 19:16

Arth

13.1k5 gold badges43 silver badges72 bronze badges

Comments

wickedone · Accepted Answer · 2013-02-16 17:29:04Z

1

Use the cross join . . .

select t1.*, t2.total1, t3.total2
from 
(
  SELECT I.id, I.custname, I.custemail, I.sku,
      DATE_FORMAT(FROM_UNIXTIME(I.ts), '%l:%i:%s %p, %c/%e/%Y') AS ts
  FROM images I
  WHERE I.stat = 0
) t1 
cross join
(
  SELECT COUNT(*) AS total1
  FROM images
  WHERE stat = 1 AND sku = ?
) t2 
cross join
(
  SELECT COUNT(*) AS total2
  FROM images
  WHERE stat = 1  AND sku IN (SELECT subsku FROM combo WHERE sku = ?) 
) t3

You might be able to make this more efficient, since they are all going after the same table. A single aggregation with a case statement per WHERE clause would probably be more efficient.

edited Feb 16, 2013 at 17:29

wickedone

5401 gold badge6 silver badges18 bronze badges

answered May 2, 2012 at 19:18

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

1 Comment

greatcaesarsghost Over a year ago

The problem I'm having with CROSS JOIN is needing the count for each sku in the first table. sku = ? would have to change to sku = t1.sku but the joined table wouldn't have that information, right?

Collectives™ on Stack Overflow

MySQL - Combining multiple queries + counts

3 Answers 3

11 Comments

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

11 Comments

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related