Query and subqueries

Databases
1

Hi all, I need your help.

With this query:


SELECT count(mat) acc 
FROM 
(SELECT dates, mat FROM tbl_acc
WHERE dates = '2010-10-27' 
GROUP BY dates, mat) q1

I have this output for date 2010-10-27: 239

Whit this other query:


SELECT app, count(mat) NAcc
FROM (SELECT app, mat FROM tbl_acc
WHERE DATES = '2010-10-27'
GROUP BY app, mat) q1 
GROUP BY app 
ORDER BY NAcc DESC

I have this other output for date 2010-10-27: 366

What is the exact result?
I think to have the same result from these different query…

Can someone help me?
Thanks in advance.

2

unless your dates and app columns have exactly the same values, then of course those queries produce different results

3

Many thanks for your help…

This is the first 58 rows of the table tbl_acc


id	mat	app	dates
1	a53	262	2010-10-27
2	a16	AG	2010-10-27
3	a0	AM	2010-10-27
4	a38	AS	2010-10-27
5	a0	CS	2010-10-27
6	a08	GS	2010-10-27
7	a94	DV	2010-10-27
8	a08	DI	2010-10-27
9	a53	D	2010-10-27
10	a14	DP	2010-10-27
11	a5	GA	2010-10-27
12	a12	GA	2010-10-27
13	a16	GA	2010-10-27
14	a30	GA	2010-10-27
15	a42	GA	2010-10-27
16	a31	GA	2010-10-27
17	a2	hp	2010-10-27
18	a5	hp	2010-10-27
19	a7	hp	2010-10-27
20	a0	hp	2010-10-27
21	a13	hp	2010-10-27
22	a16	hp	2010-10-27
23	a38	hp	2010-10-27
24	a27	hp	2010-10-27
25	a74	hp	2010-10-27
26	a36	hp	2010-10-27
27	a53	hp	2010-10-27
28	a94	hp	2010-10-27
29	a28	hp	2010-10-27
30	a59	hp	2010-10-27
31	a13	LU	2010-10-27
32	a53	LU	2010-10-27
33	a5	MN	2010-10-27
34	a74	MN	2010-10-27
35	a53	MN	2010-10-27
36	a2	NI	2010-10-27
37	a53	NI	2010-10-27
38	a2	PT	2010-10-27
39	a9	PT	2010-10-27
40	a88	PT	2010-10-27
41	a09	PT	2010-10-27
42	a41	PT	2010-10-27
43	a53	QT	2010-10-27
44	a1	QI	2010-10-27
45	a53	QI	2010-10-27
46	a61	QI	2010-10-27
47	a44	QI	2010-10-27
48	a53	RA	2010-10-27
49	a60	RP	2010-10-27
50	a7	RT	2010-10-27
51	a8	RT	2010-10-27
52	a16	RT	2010-10-27
53	a28	RT	2010-10-27
54	a53	ST	2010-10-27
55	a53	TT	2010-10-27
56	a53	VS	2010-10-27
57	a94	VS	2010-10-27
58	a53	VE	2010-10-27
....

I think to have the same result from these different query.
:confused:

4

your first subquery produces this –


SELECT dates, mat FROM tbl_acc
WHERE dates = '2010-10-27' 
GROUP BY dates, mat

dates,mat
2010-10-27,a0
2010-10-27,a08
2010-10-27,a09
2010-10-27,a1
2010-10-27,a12
2010-10-27,a13
2010-10-27,a14
2010-10-27,a16
2010-10-27,a2
2010-10-27,a27
2010-10-27,a28
2010-10-27,a30
2010-10-27,a31
2010-10-27,a36
2010-10-27,a38
2010-10-27,a41
2010-10-27,a42
2010-10-27,a44
2010-10-27,a5
2010-10-27,a53
2010-10-27,a59
2010-10-27,a60
2010-10-27,a61
2010-10-27,a7
2010-10-27,a74
2010-10-27,a8
2010-10-27,a88
2010-10-27,a9
2010-10-27,a94

then your first query counts these, so it produces 1 row of output consisting of 1 column (containing the number 29, which is the number of rows in the subquery)

your second subquery produces this –


SELECT app, mat FROM tbl_acc
WHERE DATES = '2010-10-27'
GROUP BY app, mat

app,mat
262,a53
AG,a16
AM,a0
AS,a38
CS,a0
D,a53
DI,a08
DP,a14
DV,a94
GA,a12
GA,a16
GA,a30
GA,a31
GA,a42
GA,a5
GS,a08
hp,a0
hp,a13
hp,a16
hp,a2
hp,a27
hp,a28
hp,a36
hp,a38
hp,a5
hp,a53
hp,a59
hp,a7
hp,a74
hp,a94
LU,a13
LU,a53
MN,a5
MN,a53
MN,a74
NI,a2
NI,a53
PT,a09
PT,a2
PT,a41
PT,a88
PT,a9
QI,a1
QI,a44
QI,a53
QI,a61
QT,a53
RA,a53
RP,a60
RT,a16
RT,a28
RT,a7
RT,a8
ST,a53
TT,a53
VE,a53
VS,a53
VS,a94

now the second query uses this data, but it produces one row for every app –


SELECT app, count(mat) NAcc
FROM (SELECT app, mat FROM tbl_acc
WHERE DATES = '2010-10-27'
GROUP BY app, mat) q1 
GROUP BY app 
ORDER BY NAcc DESC

app,NAcc
hp,14
GA,6
PT,5
QI,4
RT,4
MN,3
VS,2
LU,2
NI,2
GS,1
VE,1
262,1
AG,1
AM,1
AS,1
CS,1
QT,1
D,1
RA,1
DI,1
RP,1
DP,1
DV,1
ST,1
TT,1

so the second query produces 25 rows of output consisting of 2 columns

please explain why you think the two queries should poroduce the same result

5

It’s true you are right, thanks for your explanation now is evident my error… thanks…