CODEWITHSUNDEEP
mysqlsqlcountsubquery
greatcaesarsghost
greatcaesarsghost

Reputation: 55

MySQL - Combining multiple queries + counts

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.

Upvotes: 1

Views: 538

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1271231

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.

Upvotes: 1

Thomas
Thomas

Reputation: 64674

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.

Upvotes: 2

Arth
Arth

Reputation: 13110

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.

Upvotes: 1

Related Questions