Count from 2 tables

Databases
1

Hi
i have 2 tables in my database cardA, and cardb, i have a field called ticket in both tables, what i want to do is if field name ticket has value of 1 do a count then output the total, so if ticket in both tables have the value of 1 the total would be 2 how do i do this, this is the code i have but outputs the total 0 not 2

$query = sprintf("SELECT cardA.ticket,cardb.ticket, COUNT(cardA.ticket) and COUNT(cardb.ticket) AS total FROM cardA, cardb GROUP BY cardA.ticket,cardb.ticket
HAVING ( COUNT(cardA.ticket) > 0 )AND( COUNT(cardb.ticket) > 0 ) ",
2

are you trying to count the values? or just the values that are 1? what’s a ticket? does ticket have any other values besides 1? does the same ticket number have to exist in both tables?

3

Hi
the tables in my database are cardA, cardb, this goes all the way to cardl but at the moment just trying to get two tables to work,
i have some bingo cards writen with flash and php, when a user chooses a ticket it writes 15 numbers into the table lets say cardA and changes the table field ticket to 1 from the default 0
so im just trying to add all the 1’s up and ouput the result so if the field ticket is still 0 it wont count.
so if they have clicked cardA and cardb the output would be 2 if they just clicked cardA and not cardb the output would be 1

4

whoa, i’m lost, that explanation is really confusing, plus the fact that you have 10 tables where probably you need only one

anyhow, are you perhaps trying to UNION the results maybe?

SELECT 'A' AS card
     , ticket
     , COUNT(*) AS total 
  FROM cardA
GROUP 
    BY ticket
UNION ALL
SELECT 'B' AS card
     , ticket
     , COUNT(*) AS total 
  FROM cardB
GROUP 
    BY ticket
5

Hi
i tried your code but still gives me a result of 0
all im trying to do is add all the 1 in the database together so if table carda field ticket has a value of 1 and table 2 cardb has a 1 in the field ticket it will add the two togeter giving me a result of 2.

6

still not making a lot of sense to me, but here’s another attempt based on your latest explanation…

SELECT COUNT(*) AS total 
  FROM ( SELECT ticket
           FROM cardA
          WHERE ticket = 1 
         UNION ALL
         SELECT ticket
           FROM cardB
          WHERE ticket = 1 
       ) AS d
7

Hi
i will try to explain what i want to do

i have 12 bingo cards with numbers on the card, a user clicks a card and it writes to the database posting all numbers, and also changing the field ticket from 0 to 1,
i have 12 tables in my database named cardA cardB cardC, and so on to 12.
all tables have a field called ticket, so if a user clicks 3 cards the output would be 3,
so i have to do some sort of count ,
count all ticket fields with 1
hope this explains.

8

i don’t think you need 12 tables for this, but that would be a separate thread :slight_smile:

did you try my latest query?

9

Hi yes i tried your query, i got no output not even 0 just blank.

10

you must’ve done something wrong in translating my query to your “real” table and column names

11

Hi
i get this when i use your query ticket=
doesn’t bring any result from the database.

12

please show the exact query that you ran

you tested it outside of php, of course?

13

I have to say I think r937’s suggestion would be better - put all the cards in a single table and call count across a single table. Otherwise your query string is going to get SERIOUSLY large when you try and union 12 tables.

14

Hi
im not sure how to put all cards into one table, it works fine the way i have it.
i thought it would be easy just to a count of all the fields named ticket.

here is the query r937 gave me
if i run the query outside php i get total 1 it should be 2

SELECT COUNT(*) AS total 
  FROM ( SELECT ticket
           FROM cardA
          WHERE ticket = 1 
         UNION ALL
         SELECT ticket
           FROM cardb
          WHERE ticket = 1 
       ) AS total",
15

in that case, you have a data problem

could you dump the table structures, please, as well as a few rows of data for each table

16

Hi i was a little brain dead yesterday late night,
i have decided to take your advise and use 1 table for all cards, if they click on a card it will just keep adding 1 to the total and then run 1 query to get the result,i will try it this way

thankyou