sql Select count from multiple tables with multiple conditions combine

[NICON]

I am wanting to combine the following two query's into a single db hit.

$db->query("SELECT id, count(*) total, 
	sum(case when type = 'Leveler OTH' then 1 else 0 end) LevOTH, 
	sum(case when type = 'Mugger OTH' then 1 else 0 end) MugOTH, 
	sum(case when type = 'Buster OTH' then 1 else 0 end) BustOTH 
	FROM othwinners 
	WHERE userid = ?");
$db->execute([$profile->id]);
								
$db->query("SELECT id, count(*) total, 
	sum(case when type = 'Leveler OTD' then 1 else 0 end) LevOTD, 
	sum(case when type = 'Mugger OTD' then 1 else 0 end) MugOTD, 
	sum(case when type = 'Buster OTD' then 1 else 0 end) BustOTD, 
	sum(case when type = 'Mobster OTD' then 1 else 0 end) MobOTD, 
	FROM otdwinners 
	WHERE userid = ?");
$db->execute([$profile->id]);

The two on there own work perfect but I am unsure if using INNER JOIN or another JOIN method is going to get me what I am looking for.

 

Both tables have the exact same columns the only difference's are in come cases the $profile->id may or may not exist and if not I will sort those with !num_rows() and the conditions for type change from one table to the other.

 

Columns are id, userid, type, howmany, and timestamp...

[Jacques1]

Both tables have the exact same columns

 

Then they probably shouldn't be two tables.

[NICON]

I certainly agree but this wasn't my masterpiece and at this time I am unable to change it without messing up other portions of the site.

[benanamen]

I am unable to change it without messing up other portions of the site.

 

So you prefer to continue building on a bad DB design? Your problems are only going to compound if you continue on that path. Your DB is the foundation of all the code you are going to write. If that is not correct, everything you build on it will not be correct.

 

The smart thing would be to make a copy of the site and do the fixes and testing locally and then backup and replace the "old" site.

 

It would also appear you have additional problems repeating data with your "types". Types should be a separate table with a unique id which you would use to reference the data. You will want to learn about "Foreign Keys".

Edited January 3, 2017 by benanamen

[NICON]

So you prefer to continue building on a bad DB design? Your problems are only going to compound if you continue on that path. Your DB is the foundation of all the code you are going to write. If that is not correct, everything you build on it will not be correct.

 

The smart thing would be to make a copy of the site and do the fixes and testing locally and then backup and replace the "old" site.

 

It would also appear you have additional problems repeating data with your "types". Types should be a separate table with a unique id which you would use to reference the data. You will want to learn about "Foreign Keys".

 

Yes I want to keep moving forward and not redo something right this second if it does not need to be. For the sake of function that is.

 

All of my types are different. Very minute but all different. Each entry has a unique id.

 

As I said both query's work independently but cant figure out how to combine them.

 

I completely understand combining the tables is a good idea. But the data in each table is not the same. One table is updated hourly vs the other which is updated daily. So in those tables there is not duplicate data. Just a unique entry hourly in the othwinners and daily in the otdwinners.

Edited January 3, 2017 by NICON

[Barand]

That said, if you want a single query, you would use a UNION and not a JOIN in this instance.

[NICON]

That said, if you want a single query, you would use a UNION and not a JOIN in this instance.

 

So go with Union despite the fact the data is not being repeated or duplicated in either table. I can UNION on the userid sense that is the only info that corresponds with data from one to the other?

[Barand]

JOINS match on key values, UNIONS do not. I think you have some reading to do

[NICON]

JOINS match on key values, UNIONS do not. I think you have some reading to do

 

Thats my fault I wasnt thinking at all there. Okay so I have combed through but my scenario seems to be very unique. This is what I have so far.

SELECT 
(SELECT id, count(*) total, 
sum(case when type = 'Leveler OTH' then 1 else 0 end) LevOTH, 
sum(case when type = 'Mugger OTH' then 1 else 0 end) MugOTH, 
sum(case when type = 'Buster OTH' then 1 else 0 end) BustOTH 
FROM othwinners 
WHERE userid = 19 
UNION ALL 
SELECT id, count(*) total, 
sum(case when type = 'Leveler OTD' then 1 else 0 end) LevOTD, 
sum(case when type = 'Mugger OTD' then 1 else 0 end) MugOTD, 
sum(case when type = 'Buster OTD' then 1 else 0 end) BustOTD, 
sum(case when type = 'Mobster OTD' then 1 else 0 end) MobOTD 
FROM otdwinners 
WHERE userid = 19)

But sql keeps giving me "The used SELECT statements have a different number of columns".

 

Now if I remove the 4th sum row in the second SELECT I get "Operand should contain 1 column(s)".

 

So I know I am doing something wrong but cant pin down what so I tried:

SELECT id, count(*) total, 
sum(case when type = 'Leveler OTH' then 1 else 0 end) LevOTH, 
sum(case when type = 'Mugger OTH' then 1 else 0 end) MugOTH, 
sum(case when type = 'Buster OTH' then 1 else 0 end) BustOTH 
FROM othwinners 
WHERE userid = 19 
UNION ALL 
SELECT id, count(*) total, 
sum(case when type = 'Leveler OTD' then 1 else 0 end) LevOTD, 
sum(case when type = 'Mugger OTD' then 1 else 0 end) MugOTD, 
sum(case when type = 'Buster OTD' then 1 else 0 end) BustOTD 
FROM otdwinners 
WHERE userid = 19

Removing the first SELECT completely and the query runs but only gives me results for the first SELECT

id total LevOTH MugOTH BustOTH
24  319    79     77      86
18   18     4      6       6

It appears there is more data to be obtained but is not showing. I base this on the value of id and total do not equate to the values of LevOTH MugOTH and BustOTH.

 

What am I missing here?

 

Edited January 3, 2017 by NICON

[NICON]

Then they probably shouldn't be two tables.

 

Jacques you are probably more right here than I initially intended. Its probably going to be much much easier to modify the 6 other files to make this a single table than fighting with this ambiguous query.

 

However I would still like to know how to accomplish this for future knowledge in the event I am working with 2 very different tables.

[Barand]

Perhaps

SELECT count(*) as total
, sum(case when type = 'Leveler OTH' then 1 else 0 end) LevOTH 
, sum(case when type = 'Mugger OTH' then 1 else 0 end) MugOTH
, sum(case when type = 'Buster OTH' then 1 else 0 end) BustOTH
, sum(case when type = 'Leveler OTD' then 1 else 0 end) LevOTD
, sum(case when type = 'Mugger OTD' then 1 else 0 end) MugOTD
, sum(case when type = 'Buster OTD' then 1 else 0 end) BustOTD
, sum(case when type = 'Mobster OTD' then 1 else 0 end) MobOTD
FROM (
    SELECT userid, type
    FROM othwinners
    WHERE userid = 19 

    UNION ALL

    SELECT userid, type
    FROM otdwinners
    WHERE userid = 19
    ) data

[NICON]

Oh my I was a mile off... So I took Jacques's advice and did this

$db->query("SELECT id, count(*) total, 
sum(case when type = 'Leveler OTH' then 1 else 0 end) LevOTH, 
sum(case when type = 'Mugger OTH' then 1 else 0 end) MugOTH, 
sum(case when type = 'Buster OTH' then 1 else 0 end) BustOTH,
sum(case when type = 'Mobster OTH' then 1 else 0 end) MobOTH, 
sum(case when type = 'Leveler OTD' then 1 else 0 end) LevOTD, 
sum(case when type = 'Mugger OTD' then 1 else 0 end) MugOTD, 
sum(case when type = 'Buster OTD' then 1 else 0 end) BustOTD, 
sum(case when type = 'Mobster OTD' then 1 else 0 end) MobOTD 
FROM ofthes_winners 
WHERE userid = ?");
$db->execute([$profile->id]);
$row = $db->fetch_row(true);

Then I fixed the crons (day and hour).

 

Then fixed the query on each page where that data was initially reflected.

$db->query("SELECT * FROM ofthes_winners WHERE type NOT LIKE '%OTH%' ORDER BY timestamp DESC LIMIT 100");
$db->execute();
$rows = $db->fetch_row();

All is functioning perfectly and my database file is now down a table it didn't need!

 

Thanks so much for everyone's help though.

[Jacques1]

The query is still a bit strange, though.

 

What is the “id” column supposed to say? There's no grouping by that column, so the value is undefined. In strict mode (and almost all other database systems), it's not even valid to select this column.

 

And why do you need to count each type “manually”? The standard way would be to just group by the type:

SELECT type, COUNT(*) as total
FROM ofthes_winners
WHERE userid = ?
GROUP BY type

This gives you the count for each type:

 type        | type_total
-------------+------------
 Leveler OTH | 14
 Mugger OTH  | 42
 ...

You can get an additional row with the overall count through WITH ROLLUP.

 

Sure, the result set is layouted “vertically” rather than “horizontally”, but that shouldn't be an issue.

[NICON]

You know that's another oversight with the id but I have it set up to echo the value for each type using:

<th width='20%'>Leveler OTH/OTD</th>
<td width='30%'>" . $row['LevOTH'] . " / " . $row['LevOTD'] . "</td>

I will tweak it a bit and see if I can get the desired output.

 

Thanks again.

[Jacques1]

Nothing prevents you from restructuring the result in your application:

<?php

// test for query result
$result_set = [
    ['type' => 'Leveler OTH', 'type_count' => 14],
    ['type' => 'Leveler OTD', 'type_count' => 42],
];

// new structure
$counts = [];
foreach ($result_set as $row)
{
    $counts[$row['type']] = $row['type_count'];
}



var_dump($counts['Leveler OTH'], $counts['Leveler OTD']);

[benanamen]

You will get better answers if you post your current DB SQL Schema with a few rows of sample data. My auto mechanic does a much better job at diagnosing my problems when he has the car in front of him. I suspect you still have other problems that need to be addressed if you want to do this correctly.

Edited January 4, 2017 by benanamen