CODEWITHSUNDEEP
sqlmysqlsubquery
meder omuraliev
meder omuraliev

Reputation: 186562

SQL: Subquery assistance

I'm trying to SELECT two values in one statement instead of two statements. The first statement counts how many entries won for a specific contest that has an id of 18. The second statement counts the total quantity of prizes for that contest.

Query 1:

SELECT 

COUNT(*) FROM entries 

WHERE entries.contest=18

AND entries.won=1

Query 2

SELECT SUM( quantity ) FROM prizes WHERE contest=18

I want to have both so I could compare them on the server-side and act upon that comparison.. So if we had say 3 entrants who won the 18th contest and the total quantity of prizes for the 18th contest is 5, do something...

Schema:

CREATE TABLE `entries` (
  `id` int(2) unsigned NOT NULL auto_increment,
  `message_id` bigint(8) unsigned default NULL,
  `user` int(2) unsigned default NULL,
  `username` varchar(50) NOT NULL,
  `contest` int(2) unsigned default NULL,
  `message` text,
  `icon` text,
  `twitter_url` text,
  `follower` int(2) unsigned default NULL,
  `entry_date` timestamp NOT NULL default '0000-00-00 00:00:00',
  `won` int(1) default '0',
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=3 DEFAULT CHARSET=utf8

CREATE TABLE `prizes` (
  `id` int(2) unsigned NOT NULL auto_increment,
  `name` varchar(25) NOT NULL,
  `contest` int(2) NOT NULL,
  `quantity` int(2) NOT NULL,
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=5 DEFAULT CHARSET=utf8

There is no users table because I control the entries, and I'm pretty sure I won't get any duplicates so that's why the user name, etc is stored for the entry row.

Upvotes: 0

Views: 136

Answers (1)

Guffa
Guffa

Reputation: 700342

As the queries doesn't have anything in common at all, you would use two subqueries to get them in the same result:

select
  (select count(*) from entries where contest = 18 and won = 1) as wins,
  (select sum(quantity) from prizes where contest = 18) as quantity

Upvotes: 2

Related Questions